SQL Server Advent Calendar 18 – Audit

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.

Technorati Tags: sql server 2008,audit,compliance,security