• Article

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 AdventureWorks database on the Person.Contacts table and sends the results to the Windows Application event log. 

Create a database-level audit

  1. 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;

  2. 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.Contact Table and the FirstName and LastName columns. */
USE AdventureWorks
GO;
CREATE DATABASE AUDIT SPECIFICATION Test_Database_Audit_Specification
FOR SERVER AUDIT Test_SQL_Server_Audit
    ADD (INSERT 
           ON Person.Contact
           BY dbo)
    WITH (STATE = ON);
GO

  3. Enable the audit.

    /* Enable the audit. */
ALTER SERVER AUDIT Test_SQL_Server_Audit
WITH (STATE = ON);
GO