Auditing events on SQL Server 2008

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

Now to achieve this, it's 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:
   SQL

   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, Execute and Permission Changes on SampleTable performed by a DBO.

3. Verify 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.

   

4. Enable both Audit and Audit Specification Object.

   

   

5. Perform any Select, Insert, Updates and Delete query to test if it's working.
6. Check if the audit is successful by going to your chosen log, for my example, I used Application Logs.

It's that easy!!!