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]