Click here to Skip to main content
Click here to Skip to main content

Auditing events on SQL Server 2008

, 28 Jun 2010
Rate this:
Please Sign up or sign in to vote.
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...

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:
    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!!!


License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Raymund Macaalay
Technical Lead
New Zealand New Zealand
http://nz.linkedin.com/in/macaalay
http://macaalay.com/
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
GeneralAuditing not Available in Standard Edition PinmemberAlex Lush9-Dec-10 4:30 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140821.2 | Last Updated 29 Jun 2010
Article Copyright 2010 by Raymund Macaalay
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid