SQL Server 2008 Hidden Gems – Audit Trail Feature

Let’s say you have a requirement to record audit trail of CRUD operations. You are using SQL Server 2008 with either Entity framework or WCF Data Services. What will you do? First you will create a database design for recording audit trail, then you will either implement triggers or interceptors to record all activities. You may want to keep it simple and may spend 4-5 days to develop a simple and efficient audit trail recording module and feel proud like myself :). Wait a minute…. you should not feel proud as you have tried to develop something which is available in SQL Server 2008 Out-of-box waiting for you to just enable it and is much more efficient and detailed then what you have developed.

How???? Have you ever noticed that when you expand the security node in SSMS, there is a folder “Audit”. What is its purpose? It is a SQL Server Audit Trail feature that no one explores and then starts implementing their own ones.

Configuring it as simple as running 2 SQL commands. Below snapshop shows you how to enable audit trail for all CRUD operations by members of an AD Group ‘TestG’ on Production.Products table of AdventureWorks:

Use [master]
Go
Create Server Audit TestAudit
To Application_Log
With (QUEUE_DELAY = 1000, ON_FAILURE = Continue, State=ON);
Go

Use [AdventureWorks]
Go
Create Database Audit Specification ProductsListAccess
For Server Audit TestAudit
ADD (Select, Insert, Update, Delete ON Production.Product by TestG)
With (State = ON);
Go

In above example, we are logging activities in Application Log (Event Viewer). You can also log audit trail in a log file and then use fn_get_audit_file function of SQL Server to read contents e.g.

SELECT * FROM fn_get_audit_file('E:\SqlAudits\*', default, default)

In the example above, we are reading all audit trails stored in E:\SQLAudits. Now what is logged and in what format? Your CRUD operation will be logged in the data field in key value format where each key is separated by a space as shown below:

As shown above, entire TSQL statement is logged as part of the audit trail. Key for the SQL statement is “statement“. You can check audit logs by expanding Server -> Security -> Audits and then selecting “View Audit Logs” from context menu of your Server Audit that you have created using above SQL statements.

No need to write code any more to implement audit trail. Just configure it as you want including events that you want to log, obects which you want to monitor, and storage of these log files.

Finally to take most out of SQL, I will recommend to use Kerberos to pass user login to SQL, then play around with schemas to define permissions on objects and if possible have SQL controlling your data security for easier reporting.

Thanks everyone for implementing all sorts of Audit Trail modules when this is there for you to use….:)