Using filtering and server audit specs to audit DB objects in SQL Server 2012

I've always been pretty "standard" in my approach to SQL Server's auditing feature. That is, Server Audit Specifications are for auditing server-level objects and Database Audit Specifications are for auditing database-level objects. There have always been a few "Audit Action Groups" that pertain to database objects that could be specified in Server Audit Specifications. An example of this would be the Audit Action Group SCHEMA_OBJECT_ACCESS_GROUP that will audit a schema-level object use of an object's permission. Or SCHEMA_OBJECT_CHANGE_GROUP that will audit a CREATE/ALTER/DROP against a schema-level object. This has been the case back to SQL Server 2008, when the auditing feature was first introduced.

The drawback of using Audit Action Groups like SCHEMA_OBJECT_ACCESS_GROUP in a Server Audit Specification is that they audit access to ANY schema object in ANY database. Server-level actions do not allow for detailed filtering on database-level actions. This is going to generate a LOT of audit records.

Enter SQL Server 2012. One of the new auditing features allows predicates to be specified on the CREATE SERVER AUDIT level. This allows much more granular auditing using Server Audit Specifications. The way that this works is to use predicates on the SERVER AUDIT object associated with a SERVER AUDIT SPECIFICATION.

Here's an example. Here's a SQL Server 2008 way to audit SELECT on the HumanResources.EmployeePayHistory table in AdventureWorks by dbo:

USE master
GO
CREATE SERVER AUDIT TestAudit1 TO file (filepath = 'c:\temp');

USE AdventureWorks
GO
CREATE DATABASE AUDIT SPECIFICATION TestDBSpec1
FOR SERVER AUDIT TestAudit1
    ADD (SELECT ON HumanResources.EmployeePayHistory BY dbo) WITH (STATE = ON);
GO

Fire up these objects (run ALTER SERVER AUDIT TestAudit1 WITH (STATE = ON); ) and you're auditing. But in SQL Server 2012, you can do (approximately) the same thing with this:

USE master
GO
CREATE SERVER AUDIT TestAudit2 TO file (filepath = 'c:\temp')
WHERE database_name ='AdventureWorks' AND schema_name ='HumanResources'
  AND object_name ='EmployeePayHistory' AND database_principal_name ='dbo';

CREATE SERVER AUDIT SPECIFICATION TestServerSpec2
FOR SERVER AUDIT TestAudit2
    — ADD SELECT ON HumanResources.EmployeePayHistory BY dbo) — note: you can't do this in SERVER AUDIT SPEC
    ADD (SCHEMA_OBJECT_ACCESS_GROUP)
    WITH (STATE = ON);
GO

Note that they're not EXACTLY the same. The Database Audit Spec is auditing only SELECT, while the filtered predicate doesn't filter on action_id (the action_id for SELECT is 'SL'). This *may* be a bug, reported on Connect currently. Although the action_id is defined as CHAR(4), specifying a string causes an error: "Msg 25713, Level 16, State 23, Line 1 The value specified for event attribute or predicate source, "action_id", event, "audit_event", is invalid.". Using a number for action_id works (and using action_id is supposed to work according to BOL) but there are no specifications of numeric action_ids. Even in sys.dm_audit_actions.

Although maybe you're not *supposed to* be able to filter on action_id, and the error message text phrasing is just a red herring. Because…

(This entry is getting a bit long, but I'll tell you what the VERY interesting point of this exercise is in the next blog entry. Stay tuned.)

@bobbeauch