SQL Audit was introduced in SQL 2008, and for the first time auditing was treated as a “first-class” object in SQL Server, meaning it could be managed by DDL statements.  It was built on the extended events framework and what made it really neat was that the event was recorded when the permission-check for that event occurred.  What this meant to us as auditors was that the event would be recorded even if it didn’t really happen because the user didn’t have permissions.  Why is this good?  Well, suppose you’re auditing the execution of a stored procedure that modifies some sensitive data, like salary information.  Wouldn’t it be nice to know not only who is executing that stored procedure, but who is trying to execute it?

One good thing about SQL Audit is that it executes asynchronously, which means it’s not going to hold up user processes.  Unfortunately, that means it also can’t access certain information, like the network login or client associated with a session.  So, going back to the salary procedure example, if a user is using a generic login to execute that procedure, you might have a hard time tracing it back to a real person.

Create the server audit

The first step in creating a SQL Audit is to create the audit object.

If you’re familiar with creating an audit in SQL Server 2008, you’ll notice a few changes in SQL 2012.  The first is the “On Audit Log Failure” selection.  In SQL 2008, this was only be checkbox to shutdown server on audit log failure.   In SQL 2012, we now have options to continue (the equivalent of not checking the old checkbox), shutdown (checking the old checkbox) or fail operation, which will fail any operation that should have been recorded but couldn’t.  This is nice if you want to prevent audited activity from going unrecorded, but don’t want to impact everything.

We have the same options for output:  a file, the Windows Application log, or the Windows Security log.  Keep in mind that, if you want to write to the Security log, some configuration is required.

The next change we see is the option for maximum files or maximum rollover files.  Maximum rollover files means that, when that number of files is reached, the oldest file will be overwritten.  If you choose Maximum files, however, once that max is reached, subsequent writes fail.

But the change that got me most excited (at first) was the new Filter tab.  My biggest beef with SQL Audit in SQL 2008 was the inability to filter out any unwanted activity or objects from the audit output.  It made for a lot of clutter.  But in SQL 2012, we now have the ability to enter a predicate to filter the audit on, i.e. “(database_name = ‘AdventureWorks2012′)”  This string is limited to 3000 characters.

Create the audit specification

The audit object tells SQL Server where to write the audit records, and how to manage them, but doesn’t actually specify what events to audit.  For that, we need to create an audit specification.

There are 2 types of audit specification: a server audit specification or a database audit specification.  Generally speaking, a server audit specification is used to audit events that occur at the server level; things like database creations, logins, creating a linked server.  A database audit specification will audit events that occur at the database level; things like executing a stored procedure, creating a user, dropping a table.  There are, however, some audit groups and events that span both levels.  You can, for example, audit the SCHEMA_OBJECT_CHANGE_GROUP at the server or the database level.  If you do it at the database level, it will only audit DDL changes in that database.  Auditing it at the server level, however, will track DDL changes in all databases.  You can create Server level audit specifications in all editions of SQL Server, however database audit specifications are only supported in Enterprise, Developer, and Evaluation editions.

Let’s say I want to audit DDL changes in the AdventureWorks2012 database.  I can create a database audit specification or I can create a server audit specification and use the new filtering functionality to limit my audit output to only AdventureWorks2012 changes.  Let’s do that.  What’s the advantage?  In this case, not much.  But let’s say you have 100 databases on this server, and you want to audit all but 5.  You could create database audit specs in 95 databases, or you could create one server audit spec and filter out the 5 databases you don’t want.  Up to you.

Activating the audit

Once I’ve created the audit and the audit specification, I’m almost ready to go.  Before SQL Server will audit anything, I need to enable both the audit and the audit specification.  I can do this by right-clicking on each and selecting “Enable” or I can do it using an ALTER statement.

Test the audit and the filter

I have my AdventureWorks2012 database.  That’s what I’m auditing.  But I also have a NoAuditDB which I’m, obviously, not auditing.  If I create a table in each database and check the Audit logs (which I do by right-clicking on the Audit and selecting “View Audit Logs”) I see only one entry, the one for AdventureWorks2012.

About that filter…

That filtering feature seems pretty handy, but what if you have a number of databases/objects/logins/etc. that you want to include or exclude from your audit?  Listing each one can become cumbersome to say the least. What if you had a table somewhere that contained all the objects to exclude from your audit, could you use a subquery in the Filter predicate?  Unfortunately, no, SQL Audit doesn’t handle this.  Bummer.

That’s ok, though.  As we’ll see soon, there’s more than one way to skin a cat.  In fact, there’s even more than one cat.