Database Level Auditing with SQL Server 2012

You normally heard about auditing in organisations, in different sectors to audit accounts which is crucial for industries. Actually auditing means inspection of company, books, accounts and documents etc.

Now what is database auditing? Database auditing is nothing but monitoring and capturing database events and activities. Auditing keeps track of user activities at database level and instance level like login, logout, execute, insert, update, delete, create, drop, alter etc.

For every audit of SQL Server database you can create only one database audit specification and Database level auditing is not available with all editions of database, it is available only in Enterprise, Developer and Evaluation editions.

You can audit at server level events and/or database level events as per your specification created. Here I am going to create step by step database level auditing with SQL Server Management Studio and T-SQL which is quite interesting and important for database professionals. Here we are creating audit on AdventureWorks2012 database.

STEP 1:

We have different options to create audit destination like on File or Security Log or Application Log. Here we will take File as our audit log destination so create one folder for audit file path (i.e.C:\AuditDemo).

STEP 2:

Now we will create New Audit.

Connect to your database instance, go to Security, right click on Audit and select New Audit… as shown below in Figure-1.

Figure 1: New Audit

STEP 3:

New Audit window will pop up where you have to write Audit name, select the state of database server On Audit Log Failure i.e. either to Continue or Shut down or Fail operation.

Audit destination I will select File to capture events but you can also choose for Security Log orApplication Log if you are on network and bound to follow windows security policies and network policies etc. Select File path as C:\AuditDemo, and choose Maximum rollover files and file size as per your requirements and click OK.

Figure 2: Create Audit

STEP 4:

Now create New Database Audit Specification.

Go to AdventureWorks2012 –> Security –> Database Audit Specifications (right click) and select New Database Audit Specification…

Figure 3: Create New Database Audit Specification

Now you have to specify Name and select Audit from drop down which we have created. Select your Audit Action Type from given drop down options as per your your requirements and click OK.

Figure 4: Create Database Audit Specification

STEP 5:

As we can see both audit and audit specifications are disabled by default so you need to Enable it as shown in Figure-5.

Figure 5: Enable Audit and Audit Specification

STEP 6:

Now, you can execute some sample queries and monitor the events in results panel where lots of interesting details are available.

USE [AdventureWorks2012]
GO
SELECT *FROM tbl_Customer
GO
EXEC usp_Customer
GO
DROP TABLE tbl_SalesOrderDetail
GO
INSERT INTO [dbo].[tbl_Customer]
 ([CustomerID],[PersonID], [StoreID], [TerritoryID]
 ,[AccountNumber] ,[rowguid], [ModifiedDate])
 VALUES
 (10001 ,55, NULL, 2, 'A100012555'
 ,'341FE9AB-9269-45DB-9926-29705A5FFC88', GETDATE())
GO
DELETE FROM [tbl_Customer] WHERE CustomerID = 10001
GO

Run below query to find the captured events.

SELECT *FROM sys.fn_get_audit_file
(
'C:\AuditDemo\*.sqlaudit',default,default 
)
GO

Figure 6: Select Captured Events

If you are T-SQL lover and wanted to do above process by query so here you can find the same.

Create Server Audit:

USE [master]
GO
CREATE SERVER AUDIT [Audit_AdventureWorks2012]
TO FILE 
( FILEPATH = N'C:\AuditDemo'
 ,MAXSIZE = 10 MB
 ,MAX_ROLLOVER_FILES = 2147483647
 ,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
 ,ON_FAILURE = CONTINUE
)
ALTER SERVER AUDIT [Audit_AdventureWorks2012] WITH (STATE = ON)
GO

Create Database Audit Specification:

USE [AdventureWorks2012]
GO
CREATE DATABASE AUDIT SPECIFICATION [Audit_DDL_Access]
FOR SERVER AUDIT [Audit_AdventureWorks2012]
ADD (SCHEMA_OBJECT_ACCESS_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP)
WITH (STATE = ON)
GO

and run few queries to capture and monitor the events as given earlier by sys.fn_get_audit_file

CLICK HERE to watch live practical.

Reference: Manzoor Siddiqui [www.SQLServerLog.com]

Comma delimited string to table

Comma delimited string to table

Here I am creating a simple function that converts a comma delimited string to table. It takes 2 arguments. One is the string and second is the delimited character. You can use any character as delimited character.

The function is ready to use. Just copy it and you can directly use it.

/*

    The function takes the string and string seperator like ', or | or anything'

    and returens a table.

    last update: 20-March-2012

    by: joydeep das

*/

IF OBJECT_ID (N'fn_CnvtTbl') IS NOT NULL

    DROP FUNCTION dbo.fn_CnvtTbl

   

GO

CREATE FUNCTION fn_CnvtTbl

    (

        @sStingVal  VARCHAR(8000) = NULL,

      @sSeperator CHAR(1) = '|'

      )

      RETURNS

            @myTBL TABLE (TblVal VARCHAR(1000))

AS

BEGIN

      DECLARE @CurrentStr VARCHAR(2000)

      DECLARE @ItemStr VARCHAR(200)

     

      IF ISNULL(@sStingVal,'')='' OR ISNULL(@sSeperator,'')=''

         BEGIN

             INSERT @myTBL (TblVal) VALUES ('Parameters not properly specified')

             RETURN

         END

        

      SET @CurrentStr = @sStingVal

     

       

      WHILE Datalength(@CurrentStr) > 0

      BEGIN

            IF CHARINDEX(@sSeperator, @CurrentStr,1) > 0

               BEGIN

                  SET @ItemStr = SUBSTRING (@CurrentStr, 1,CHARINDEX(@sSeperator, 

                                            @CurrentStr,1) - 1)

                  SET @CurrentStr = SUBSTRING (@CurrentStr,CHARINDEX(@sSeperator,

                                               @CurrentStr,1) + 1,

                                              (Datalength(@CurrentStr)-

                                               CHARINDEX(@sSeperator,@CurrentStr,1) + 1))

                  INSERT @myTBL (TblVal) VALUES (@ItemStr)

                END

             ELSE

                BEGIN               

                  INSERT @myTBL (TblVal) VALUES (@CurrentStr)              

                  BREAK;

                END

      END

      RETURN

END

/*

to test the function use

SELECT * FROM dbo.fn_CnvtTbl('RAM,SHAM,JADU,MADHU',',')

*/   

Hope you like it.

Don’t use the KEYWORD in COLUMNS name

Don’t use the KEYWORD in COLUMNS name

The SQL server allows to use the Keywords in Table columns name by using this [ …].

But I think it is not a good idea to use the key words especially if you want migrate the table from another RDBMS. And is not a good practice to use the key words in table columns name.

Suppose you have a database with 1000 of table objects. Now your boss told you to alter the columns name that has the name used by Keywords. Is it possible manually or you just resigned the job.

Here in my article, I am trying to make a function that search the table name and related columns name that have the Keywords used.

So don't think about resignation or others thing – just joking.

The function is ready to use you can add more key words to enrich the functionality of the function.

CREATE  FUNCTION dbo.fn_ChkKeyWord

            (

                  @param_sWord  VARCHAR(255)

            )     RETURNS BIT

AS

BEGIN

      DECLARE @isWorking VARCHAR(255)

      DECLARE @bitRETRUN BIT

     

      SET @isWorking = UPPER(LTRIM(RTRIM(@param_sWord)))

      -- You can Add more Key word to Enrich the Functionality

      SET @bitRETRUN = CASE WHEN @isWorking

      IN('ADD', 'ALL', 'ALTER', 'AND', 'ANY', 'AS', 'ASC','AUTHORIZATION'

      , 'BACKUP', 'BEGIN', 'BETWEEN', 'BREAK', 'BROWSE', 'BULK', 'BY'

      , 'CASCADE', 'CASE', 'CHECK', 'CHECKPOINT', 'CLOSE', 'CLUSTERED'

      , 'COALESCE', 'COLLATE', 'COLUMN', 'COMMIT', 'COMPUTE','CONSTRAINT'

      , 'CONTAINS', 'CONTAINSTABLE', 'CONTINUE', 'CONVERT', 'CREATE'

      , 'CROSS', 'CURRENT', 'CURRENT_DATE', 'CURRENT_TIME'

      , 'CURRENT_TIMESTAMP', 'CURRENT_USER', 'CURSOR', 'DATABASE','DBCC'

      , 'DEALLOCATE', 'DECLARE', 'DEFAULT', 'DELETE', 'DENY', 'DESC'

      , 'DISK', 'DISTINCT', 'DISTRIBUTED', 'DOUBLE', 'DROP', 'DUMMY'

      , 'DUMP', 'ELSE', 'END', 'ERRLVL', 'ESCAPE', 'EXCEPT', 'EXEC'

      , 'EXECUTE', 'EXISTS', 'EXIT', 'FETCH', 'FILE', 'FILLFACTOR','FOR'

      , 'FOREIGN', 'FREETEXT', 'FREETEXTTABLE', 'FROM', 'FULL','FUNCTION'

      , 'GOTO', 'GRANT', 'GROUP', 'HAVING', 'HOLDLOCK', 'IDENTITY'

      , 'IDENTITY_INSERT', 'IDENTITYCOL', 'IF', 'IN', 'INDEX', 'INNER'

      , 'INSERT', 'INTERSECT', 'INTO', 'IS', 'JOIN', 'KEY', 'KILL','LEFT'

      , 'LIKE', 'LINENO', 'LOAD', 'NATIONAL', 'NOCHECK', 'NONCLUSTERED'

      , 'NOT', 'NULL', 'NULLIF', 'OF', 'OFF', 'OFFSETS', 'ON', 'OPEN'

      , 'OPENDATASOURCE', 'OPENQUERY', 'OPENROWSET', 'OPENXML','OPTION'

      , 'OR', 'ORDER', 'OUTER', 'OVER', 'PERCENT', 'PLAN', 'PRECISION'

      , 'PRIMARY', 'PRINT', 'PROC', 'PROCEDURE', 'PUBLIC', 'RAISERROR'

      , 'READ', 'READTEXT', 'RECONFIGURE', 'REFERENCES', 'REPLICATION'

      , 'RESTORE', 'RESTRICT', 'RETURN', 'REVOKE', 'RIGHT', 'ROLLBACK'

      , 'ROWCOUNT', 'ROWGUIDCOL', 'RULE', 'SAVE', 'SCHEMA', 'SELECT'

      , 'SESSION_USER', 'SET', 'SETUSER', 'SHUTDOWN', 'SOME','STATISTICS'

      , 'SYSTEM_USER', 'TABLE', 'TEXTSIZE', 'THEN', 'TO', 'TOP',       

      ,'TRANSACTION', 'TRIGGER', 'TRUNCATE', 'TSEQUAL', 'UNION','UNIQUE',    

      ,'UPDATE', 'UPDATETEXT', 'USE', 'USER', 'VALUES', 'VARYING','VIEW'

      , 'WAITFOR', 'WHEN', 'WHERE', 'WHILE', 'WITH', 'WRITETEXT')

THEN 1 ELSE 0 END

      RETURN @bitRETRUN

END

GO

-- To Check

CREATE TABLE My_KeyWordTable

   (

      [END]    char(10) NOT NULL ,

      [VALUES] char(10)  NULL ,

      [CROSS]  char(10)   NULL

   )

SELECT TABLE_NAME, COLUMN_NAME

FROM   INFORMATION_SCHEMA.COLUMNS

WHERE  1=dbo.fn_ChkKeyWord(COLUMN_NAME)

Normal Vs Correlated Sub Query

Normal Vs Correlated Sub Query

In this article I am trying to explain related to normal and correlated subquery. It is a short article but gives you a solid concept related to it.

Normal or no correlated subquery

In normal or no correlated subquery, the inner subquery portion is independent; it not depends on outer query. The result of inner query helps to execute the outer query. The outer query is depends on the result or output of the inner query. The inner query is completely independent and can execute independently.

USE Products

SELECT *

FROM   Product AS P1

WHERE  P1.Unit = (SELECT MAX(P2.Unit)

                  FROM   Product AS P2

                  WHERE  P2.ProductName = 'Tooth Brush');

Correlated or repeatingsub

But in the correlated or repeating subquery, the subquery is depends on outer query for it's values. This means the subquery is executed repeatedly once for each row that might be selected by the outer query.

USE Products

SELECT *

FROM   Product AS P1

WHERE  P1.Unit = (SELECT MAX(P2.Unit)

                  FROM   Product AS P2

                  WHERE  P2.ProductName = P1.ProductName);

Summary related to Performance

Subqueries are a powerful feature of SQL. However, subqueries that contain an outer reference can be very inefficient. In many instances these queries can be rewritten to remove the outer reference which can improve performance. It is worthwhile to review the SQL Execution plan to help identify potential inefficiencies.

Hope you like that