SQL Server 2012: User-defined Audit April 2nd, 2013

Vinod Kumar

Coming back from TechEd 2013, I have loads to share. I have done a number of sessions and I am glad I could meet all of you at the event. This blog gets inspired from what was discussed during the event. One of the lesser known features introduced with SQL Server 2012 is the ability to have User-defined Audits inside SQL Server.

Let me start by creating the normal steps of creation of Audits. Click Security –> Audits –> Right Click “New Audit”.

image

Here we are configuring the “WHERE” part of Audit. Once clicking the OK, make sure to right click and Enable the Audit.

The next section involves the “WHAT” are we going to audit. Click Security –> Server Audit Specification –> Right Click “New Server Audit Specification …”.

image

Give it a Name and select the Audit we created in the previous step. Under Audit Action Type, select “USER_DEFINED_AUDIT_GROUP”. This is the new Action type introduced with SQL Server 2012. Click OK to complete the step. Donot forget to Right Click and Enable this Server Audit Specification.

Now that we have created both where we are going to audit and what we are going to audit. The next step is to create the audit.

image

SQL Server 2012 introduces the new stored proc “sys.sp_audit_write” and this invokes a note on our Audit.

EXEC sys.sp_audit_write 1, 1, N’1 – This is additional Information’
GO

Above is the command for your reference. The second parameter can be used for success or failure. 0=Failure while 1=Success – feel free to use it accordingly.

Once the above statements are fired, we can view the same on our Audit file. Select Security –> Audit –> UserDefinedAudit –> Right Click “View Audit Logs”. We will see 3 records. One to start the service and the two based on our commands.

image

In this log we can see the command statement, Succeeded as False, and User Defined Information contains the text we wrote. So this is complete in all sense.

Finally, I during the demo I also showed how we can read the Audit files located in a location using TSQL command sys.fn_get_audit_file command. All the UI details are also available via this TSQL too.

image

Hope you found this interesting and do tell me if you will be using the same in your environments.

Tags: , , , , , , , , ,

This entry was posted on Tuesday, April 2nd, 2013 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.


5 Responses to “SQL Server 2012: User-defined Audit”

  1. Yousuf says:

    Hi Sir, you explaintion was really help full in classes..
    Thank you so much…

  2. Sudhir says:

    Hi Vinod,
    As ever, nice and informative explanation.
    There seems to be a typo / grammatical error in the sentence – “Here we created are creating having configuring.. “. Could you please correct it

    Thanks,
    Sudhir

  3. Naga Sirisha Myneedu says:

    Detailed explanation. Thanks Vinod.
    Waiting to attend Tech-ed 2013 Australia.

Leave a Reply