Share via


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

  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.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
    
  3. Enable the audit.

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