• Article

How to: Create a Server-level Audit

Before you can create a server-level audit specification you must create and configure a SQL Server Audit object that can be used for the server audit.

Accomplishing this task involves using Query Editor in SQL Server Management Studio to carry out the following procedure. The following example creates a server-level audit of failed logon actions, sending the audit to the Windows Application event log.

To create a server-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 server audit specification and map it to the Audit object.

    /* Create the Server Audit Specification object by using an Audit  event group. */
CREATE SERVER AUDIT SPECIFICATION Test_Server_Audit_Specification
FOR SERVER AUDIT Test_SQL_Server_Audit
    ADD (FAILED_LOGIN_GROUP);

  3. Enable the audit.

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