SQL Audit – The Basics

I’ve implemented a variety of auditing solutions in the past, but they’ve always been rather complicated endeavors, custom built, in need of documentation for other DBAs to understand. For system administrators, they’re incomprehensible.

However in reading the Auditing in SQL Server 2008 paper, I learned that the new auditing features in SQL Server 2008 make setting up a basic set of audits fairly simple. As with most simple systems, there still are plenty of caveats, and you can shoot yourself in the foot, but this does let you handle a few simple tasks quickly.

The basic architecture of SQL Audit is like this:

Audit

---- 1:1 - Server Audit Speciation

----- 1:n – Database Audit Specification

This is kind of similar to the Policy Based Management setup, simple, flexible, and therefore somewhat confusing. You can create an audit, but it doesn’t necessarily have anything in it. Think of this as being analogous to a trace. However unlike a trace, which required all sorts of stored procedure calls, the new SQL Audit objects are first class objects, meaning there are DDL statements (CREATE, ALTER, etc.) that you can use.

You can then add a single server level specification to this audit, which covers server level stuff. You can read more about those events here (and I’ll blog more on these).

You can also add multiple database level audit specifications, things you want to audit, to this audit. Each of these can be auditing different actions, and they can be somewhat general, or very specific.

Sounds confusing, and it can be, but I’ll work on simplifying things in future posts. For now, understand that it is a powerful system that can allow you to build auditing in much simpler ways than in the past.