SQL Server Portal

In my earlier article, I discussed a lot of different enhancements of Audit features in SQL Server 2012. Today we are going to implement these features step by step.

Step 1 :
In this step, we need to create an audit at server level and set the options as per the requirement.
Given below are the two different methods to create Audit.

(1) Using SQL Server Management Studio :
To create Audit you need to go to Object Explorer, expand the security node (folder) after that right click the Audits folder and select New Audit as shown in the picture below.

(2) Using Transact-SQL :
Given below is the script to create an audit on server.

USE [master]
GO

CREATE SERVER AUDIT [Audit_Sample]
TO FILE
( FILEPATH = N'C:\Audit\'
)

ALTER SERVER AUDIT [Audit_Sample] WITH (STATE = ON)
GO

Step 2 :
In this step, we need to create an audit specification on the specific database. Also, we can setup the criteria for audit as well.
Given below are the two methods to achieve it.

(1) Using SQL Server Management Studio :
To create Audit specification, you need to go to Object Explorer, expand the security node (folder) of that particular database and then right click the Database Audits Specifications folder and select New Database Audit Specification… as shown in the picture below.

(2) Using Transact-SQL :
Given below is the script to create an Audit Specification on Adventure database.

USE [AdventureWorks2012]
GO

CREATE DATABASE AUDIT SPECIFICATION [FilterFor_HumanResource_Data]
FOR SERVER AUDIT [Audit_Sample]
ADD (SELECT ON SCHEMA::[HumanResources] BY [public])
WITH (STATE = ON)
GO

Step 3 :
Once Audit and Audit Specifications are done, lets browse any table within the [HumanResources] Schema to show its reflection in audit report.

USE [AdventureWorks2012]
GO
SELECT * FROM HumanResources.Department
GO

Step 4 :

Lets view the audit report using fn_get_audit_file

USE [AdventureWorks2012]
GO
SELECT * FROM fn_get_audit_file
('C:\Audit\Audit_Sample_*.sqlaudit',default,default);
GO

Conclusion :
I captured only few areas of Audit, but there is much more to do in it. Also, you can easily configure it in such a way that it can reduce your troubleshoot time.

Let me know if you used this feature in real world example along with its benefits.