Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

SQL Server Advent Calendar 18 – Audit

SQL Server Advent Calendar 18 – Audit

  • Comments 3
  • Likes

Day 18 of my virtual advent calendar, about stuff I like in SQL Server 2008..

Rounding out ways of keeping an eye on what’s happening to your data in SQL Server 2008 is the new auditing feature in Enterprise edition.  As the name suggest it’s there for a specific purpose, to make your compliance work as easy as possible.

In SQL Server 2005 you could monitor changes to to the data in a database using triggers and you could also track changes to permissions and schemas.  However tracking the results of queries was not easy or you had to resort to profiler to trap the SQL.  Audit not only handles all of this it, also allows you to direct the audit information to the application or security log as well as to file.

You also get a nice UI to help you set it up and consume the information.

The first step is to create an Audit in the Security node of SSMS..

image 

This will consume i.e. be the target of the database audit specification.  I am going to use a file here.

Now I can setup a database audit specification again using the UI which I can get to by expanding security under the database I am interested in and right clicking on Database Audit Specifications..

image

I am going for an action type of select on an object and then I can select which object(s) to track. In this case I am worried about people looking up employee addresses so I have just selected the HumanResources.EmployeeAddress table. I can also filter by the security principal  (in my case dbo) as I may only be worried about certain groups of users.  Having created it I can right click on it to enable it and I am done.

To test it I can run some queries against it..

select
    E.LoginID,
    A.AddressLine1,
    A.City
from HumanResources.EmployeeAddress EA
inner join Person.Address A on EA.AddressID = A.AddressID
inner join HumanResources.Employee E on E.EmployeeID = EA.EmployeeID

Select
    VE.FirstName,
    VE.AddressLine1,
    VE.City
from HumanResources.vEmployee VE

Note the second query is a view based on the table we have the audit on.

Now I can see the output by going back to the audit and right clicking to view the audit log..

image

scanning along to the right I can see the SQL I used…

image

so we got both selects back which is good to know, and I set this up in the the time it took my wife to grill tonight’s Salmon! 

For further reading check books on-line here and this whitepaper on SQL Server 2008 security.

Comments
  • PingBack from http://bestwebhostingservices.com/andrew-fryers-blog-sql-server-advent-calendar-18-%e2%80%93-audit/

  • Hi, this is a good feature.

    Does this work for CLR Stored procedures ?

    Usually you cant see in CLR C# Stored procedure as its compiled ?

    Thanks

    Gary

  • Gary

    It will still work, in my demo you will see the select statement that bought the data into the stored proc. However it won't show you the name of the proc itself, in my exmaple you also didn't see the name of the view so you would have to audit the use of those related objects separately and then join up the related queries. Bear in mind this is only showing what's ahppenning at the database level.

    Hope this helps

    Andrew

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment