SQL Server 2008 Auditing

There have been instances where I have had the need to record the last time a table was viewed, SELECT(ed) or even been requested to provide a record of who was accessing specific records. Previous to sql server 2008 the ability to perform such a task was limited to creating a custom trace in profiler which could result in un-needed overhead in a production environment. SQL Server 2008 provides the ability to create such a trace natively using the new sql server audit.


Creating the Objects


In this example I will create a database audit specification that will monitor all select statements made against the Person.Person table in the AdventureWorks2008 database from the role of dbo. The first step is to create a server-level audit that will define the audit name and where and how the audit will be stored. These objects can be created using the object explorer in SSMS, but I will demonstrate how to create them using t-sql in this illustration. You have the ability to store the audit log either in the application log, security log, or a file path. In this example I will use a file path to store the audit log stored in the Audit folder on the C drive using the below listed code:

--Server level audit must be created in the master database
USE master
CREATE SERVER AUDIT Select_Audit
TO FILE(FILEPATH='C:\AUDIT\')GOALTER SERVER AUDIT Select_AuditWITH (STATE=ON)GO

All server-level audits must be created in the master database and after creating the audit you must set the state to on using an alter statement as it cannot be included in the create statement. You will probably notice that only a path is declared and not a file name as the file name will be generated automatically based on the audit name and guid. The server level audit can also be viewed in the Object Explorer under the server Security folder in the Audits folder.

Once this is complete you can create your database audit specification that will declare the server level audit you just created. The below statement creates a database audit specification named Person_Select that will log all select queries against the Person.Person table made from the principal dbo:

USE AdventureWorks2008
CREATE DATABASE AUDIT SPECIFICATION Person_Select FOR SERVER AUDIT Select_Audit  
ADD(SELECT ON Person.Person BY dbo)
  WITH (STATE = ON)GO

Again the audit specification is contained under the database Security folder in the Database Audit Specifications folder. Once the audit specification is created assure that you are logged in as a user in the dbo role and execute the statements below.

SELECT *
FROM Person.Person;
GOSELECT *FROM Person.PersonWHERE LastName = 'Duffy';
GO

You are able to view the audit file using the system stored procedure sys.fn_get_audit_file

SELECT event_time,
      action_id,
      session_server_principal_name,
      statementFROM sys.fn.get_audit_file('C:\Audit\*', DEFAULT, DEFAULT)

The results appear as this:

In my from clause I included the file path followed by a *, which specifies to read all audit logs within the specified path. The full documentation for the new auditing capability and relative functions can be found in sql server books on line along with several examples.




Conclusions

This provided a simple example of how to implement sql server 2008’s new auditing features. There are a numerous features and configurations that I did not touch on, but full documentation can be found in sql server 2008 books on line and on msdn at the below link:

MSDN SQL Audit