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
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 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);
Enable the audit.
/* Enable the audit. */ ALTER SERVER AUDIT Test_SQL_Server_Audit WITH (STATE = ON); GO