… Continued from previous blog entry …
The point of using a Server Audit Specification with database events in SQL Server 2012 is this. In SQL Server 2008, audit is an Enterprise-only feature. Only available in Enterprise, Evaluation, and Developer Edition. In SQL Server 2012, one of the new features (to quote BOL) is: "Support for server auditing is expanded to include all editions of SQL Server. Database audits are limited to Enterprise, Datacenter, Developer, and Evaluation editions." (Note: there's no DataCenter edition any more after the recent licensing change).
So there IS a SQL Express 2012 RC0 available. Let's download it and try it out. [Starts download. Waits 5 minutes. Starts install. Done in 5-10 minutes. Nice].
There is no GUI for Server Audit and Server Audit Specification, even when you load up an Express edition into a Enterprise SSMS Object Explorer. But who needs a GUI…we can use DDL and use sys.fn_get_audit_file to read the audit log.
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
As my friends in the UK would say: "Works a treat!". And Books Online is correct, attempting to define a Database Audit
Specification in SQL Express returns:
Msg 33075, Level 16, State 3, Line 1
Granular auditing is not available in this edition of SQL Server. For more information about feature support in the editions of SQL Server, see SQL Server Books Online.
So you can't do without Database Audit Specs is audit Database Level audit actions. Like INSERT, SELECT, or DELETE. So maybe you're not *supposed to* be able to specify action_id in a Server Audit Specifcation filter predicate. Although you can't specify these (action_ids in predicate filters) for Server-level objects either. Even in Enterprise edition.
Still, having seen auditing with SQL Express and only SERVER AUDIT filters, it's a MUCH more powerful and compelling feature than I'd imaged when I first heard of it. Especially after seeing folks try to shoehorn auditing using CDC. Or change tracking. Or use triggers on every action. And BTW, you are limited to 3000 characters in a filter predicate, although they seem to allow AND/OR/NOT and the standard comparison operators. No "LIKE" capability, but you don't get that it SQL Server auditing anyhow.
In any case… enjoy auditing in SQL Express! And Standard Edition. And BI Edition. And no, I haven't tried it on LocalDB yet.
@bobbeauch