Auditing events on SQL Server 2008

If you want to audit whats happening on your SQL Server then look no further as SQL Server 2008 have a built in function for that, so you can log all Queries like Select, Insert, Updates and Deletes perfromed on a cetain table easily, before you will be needing third party tools to do this or use SQL Trace which slows down your database.

Now to achive this its as easy as 6 steps.

1. Create an Audit, you can easily do that by going the security section and Audits.

It will just ask you for an Audit Name and where to save and what to save as File, Security Log or Application Log

2. Now you have an Audit, you need to create an Audit Specification by Database you want to audit.  Here is a script on how to do it

USE SampleDatabase
CREATE DATABASE AUDIT SPECIFICATION TestAuditSpecification
FOR SERVER AUDIT TestAudit
 ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
 ADD (SELECT ON dbo.SampleTable BY dbo),
 ADD (INSERT ON dbo.SampleTable BY dbo),
 ADD (UPDATE ON dbo.SampleTable BY dbo),
 ADD (DELETE ON dbo.SampleTable BY dbo),
 ADD (EXECUTE ON dbo.SampleTable BY dbo)

That sample will audit all Select, Insert, Update, Delete, Exceute and Permission Changes on SampleTable performed by a dbo.

3. Verfiy if the script created the object by going to the database’s security section under database audit specifications.

Double click it if you want to view or alter any specification you want.

Different Audit Action types can be chosen on the menu and add what you need.  You can view the definitions here http://technet.microsoft.com/en-us/library/cc280663.aspx

4. Enable both Audit and Audit Specification Object

5. Perform any Select, Insert, Updates and Delete query to test if its working.

6. Check if the audit is successful by going to your chosen log, for my example I used Application Logs.

Its that easy!!!