\nI'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.\n<\/p>\n
\nThe 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.\n<\/p>\n
\nEnter 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.\n<\/p>\n
\nHere's an example. Here's a SQL Server 2008 way to audit SELECT on the HumanResources.EmployeePayHistory table in AdventureWorks by dbo:\n<\/p>\n
\nUSE master
\nGO
\nCREATE SERVER AUDIT TestAudit1 TO file (filepath = 'c:\\temp');\n<\/p>\n
\nUSE AdventureWorks
\nGO
\nCREATE DATABASE AUDIT SPECIFICATION TestDBSpec1
\nFOR SERVER AUDIT TestAudit1
\n ADD (SELECT ON HumanResources.EmployeePayHistory BY dbo) WITH (STATE = ON);
\nGO\n<\/p>\n
\nFire 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:\n<\/p>\n
\nUSE master
\nGO
\nCREATE SERVER AUDIT TestAudit2 TO file (filepath = 'c:\\temp')
\nWHERE database_name ='AdventureWorks' AND schema_name ='HumanResources'
\n AND object_name ='EmployeePayHistory' AND database_principal_name ='dbo';\n<\/p>\n
\nCREATE SERVER AUDIT SPECIFICATION TestServerSpec2
\nFOR SERVER AUDIT TestAudit2
\n — ADD SELECT ON HumanResources.EmployeePayHistory BY dbo) — note: you can't do this in SERVER AUDIT SPEC
\n ADD (SCHEMA_OBJECT_ACCESS_GROUP)
\n WITH (STATE = ON);
\nGO\n<\/p>\n