How to set Database Audit Specification for all the tables in db

I need to create an audit to track all CRUD events for all the tables in a database , now i have more than 100 tables in the DB , is there a way to create the specification which will include all the tables in the DB ?开发者_如何学Go

P.S : I am using SQL Server 2008

---

I had the same question. The answer is actually simpler than expected and doesn't need a custom C# app to generate lots of SQL to cover all the tables. Example SQL below. The important point was to specify database and public for INSERT/UPDATE/DELETE.

USE [master]
GO

CREATE SERVER AUDIT [CancerStatsAudit]
TO FILE 
(   FILEPATH = N'I:\CancerStats\Audit\'
    ,MAXSIZE = 128 MB
    ,MAX_ROLLOVER_FILES = 64
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = '5a0a18cf-fe42-4171-ad01-5e19af9e27d1'
)
ALTER SERVER AUDIT [CancerStatsAudit] WITH (STATE = ON)
GO

USE [CancerStats]
GO

CREATE DATABASE AUDIT SPECIFICATION [CancerStatsDBAudit]
FOR SERVER AUDIT [CancerStatsAudit]
ADD (INSERT ON DATABASE::[CancerStats] BY [public]),
ADD (UPDATE ON DATABASE::[CancerStats] BY [public]),
ADD (DELETE ON DATABASE::[CancerStats] BY [public]),
ADD (EXECUTE ON DATABASE::[CancerStats] BY [public]),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON)
GO

NB: DATABASE_OBJECT_CHANGE_GROUP and SCHEMA_OBJECT_CHANGE_GROUP are not needed for auditing INSERT, UPDATE and DELETE - see additional notes below.

Additional notes:

The example above also includes the DATABASE_OBJECT_CHANGE_GROUP and the SCHEMA_OBJECT_CHANGE_GROUP. These were included since my requirement was to also track CREATE/ALTER/DROP actions on database objects. It is worth noting that the documentation is wrong for these. https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions

The above page states DATABASE_OBJECT_CHANGE_GROUP tracks CREATE, UPDATE and DELETE. This is not true (I've tested in SQL Server 2016), only CREATE is tracked, see: https://connect.microsoft.com/SQLServer/feedback/details/370103/database-object-change-group-audit-group-does-not-audit-drop-proc

In fact, to track CREATE, UPDATE, DELETE, use SCHEMA_OBJECT_CHANGE_GROUP. Despite the above learn.microsoft.com documentation page suggesting this only works for schemas, it actually works for objects within the schema as well.

---

Change Data Capture

You can use the Change Data Capture functionality mechanism provided by SQL Server 2008.

http://msdn.microsoft.com/en-us/library/bb522489.aspx

Note that this will only do Create, Update and Delete.

Triggers and Audit tables

Even for 100 tables, you can use a single script that will generate the audit tables and the necessary triggers. Note, that this is not a very good mechanism - it will slow down the control will not be returned unless the trigger execution is complete.

---

Found a way to create Database Audit specification , wrote a c# code that dynamically generated sql statement for all the tables and all the actions I needed and executed the resultant string. Frankly the wizard provided is no help at all if you are creating a Database Audit Specification for more than a couple of tables.