Cleveland DBA

SQL Server administration in Cleveland, OH

SQL Server A to Z – Audit

In previous versions of SQL Server, there were a number of methods for auditing activity within an instance or database.  You could use DDL triggers to track any DDL changes, login auditing to write login successes or failures to the error log, Profiler tracing, and even C2/Common Criteria Compliance Auditing.  But each of these methods had its shortcomings.  It was either too limited in scope or granularity or just too hard to administer.

Introducing SQL Server Audit

With SQL Server 2008 we’re introduced to a more comprehensive solution called SQL Server Audit.  With SQL Server Audit you can track events at the instance or database level.  You can audit login failures at the server level, for example, or track object changes within a database, or even to audit T-SQL statements being issued against a specific object.

Create the Audit object

To get started using SQL Server Audit, we need to create an Audit Object.  This can be done through SSMS or T-SQL.  Using SSMS, right-click the Audit folder under Security and select New Audit.  Give the new audit a name and specify where you want the output to go.  You can choose to write to a file, the Application Log, or the Security Log.  Personally, I like to output to a separate file.  If you choose File, specify the path where you want the output file stored and, optionally the max size for the file.  Click OK.

Create the Audit Specification

Now that we’ve created the audit, we need to tell it what to track.  To do this, we create an audit specification.  Audit specifications can be created at the server level or at the database level, each having its own set of actions.  For the sake of this demonstration, let’s say I want to audit any DDL changes made to objects in the AdventureWorks database.  Within the AdventureWorks database in SSMS, right-click Database Audit Specifications under Security, and select New Database Audit Specification…  Give your spec a name and select the audit we just created.  Under Actions, for the Audit Action Type, choose SCHEMA_OBJECT_CHANGE_GROUP and click OK.

Enable the Audit and the Audit Specifications

You’ll notice that both the Audit and the Audit Specification are created disabled.  Right-click on each and enable them.  Now we’re ready to test it.

USE AdventureWorks
CREATE table AuditDemo (
Column1 int);

To view the audit log, right-click on the Audit object and select View Audit Logs.

And there you have it.  Easier than Profiler, less invasive than triggers, and more specific than C2 auditing.  I was impressed by the sheer number of events you can audit and how easy this was to implement.  What do you think?  Do you plan on using SQL Server Audit in your environment?

July 8, 2011 Posted by | Features | , , | Leave a comment