How to: Create a Database-Level Audit
Before you can create a database-level audit specification you must create and configure a SQL Server Audit object that can be used for the database audit.
Accomplishing this task involves using Query Editor in SQL Server Management Studio to carry out the following procedure. The following example creates a database-level audit of any insert operations in the AdventureWorks2008R2 database on the Person.Person table and sends the results to the Windows Application event log.
Create a database-level audit
Create an Audit object and define the target.
/* Create the SQL Server Audit object, and send the results to the Windows Application event log. */ CREATE SERVER AUDIT Test_SQL_Server_Audit TO APPLICATION_LOG /* The Queue Delay is set to 1000, meaning one second intervals to write to the target. */ WITH ( QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE); GO;
Create the database audit specification and map it to the Audit object.
/* Create the Database Audit Specification object using an Audit event for the Person.Person Table and the FirstName and LastName columns. */ USE AdventureWorks2008R2; GO; CREATE DATABASE AUDIT SPECIFICATION Test_Database_Audit_Specification FOR SERVER AUDIT Test_SQL_Server_Audit ADD (INSERT ON Person.Person BY dbo) WITH (STATE = ON); GO
Enable the audit.
/* Enable the audit. */ ALTER SERVER AUDIT Test_SQL_Server_Audit WITH (STATE = ON); GO