How to: Create a Server Audit and Server Audit Specification

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

Accomplishing this task involves using Object Explorer in SQL Server Management Studio to carry out the following procedures.

The following example creates an audit specification named "ServerAudit1" that sends the audit output to the Windows Application event log. This specification is then used to create a server-level audit for the FAILED_LOGIN_GROUP on the default server instance.

To create a new audit specification

  1. In Object Explorer, recursively expand the Security node down to Audits.

  2. Right-click Audits and then click New Audit. This opens the Create New Audit page.

  3. In the Audit name field, type ServerAudit1.

  4. For Audit destination, select ApplicationLog from the list.

  5. Click OK to accept the default settings and save the new audit specification.

To create a server-level audit specification

  1. In Object Explorer, right-click Server Audit Specifications and then click New Server Audit Specification. This opens the Create Server Audit Specification page.

  2. In the Name field, type AdventureworksServerAudit1.

  3. For Server Audit, select ServerAudit1 from the list.

  4. Using the table grid, click the row that is prefixed by the asterisk (*). For the Audit Action Type, pick FAILED_LOGIN_GROUP from the list.

  5. Click OK to save the server audit specification.

  6. Expand the Audits node and right-click ServerAudit1. Click Enable Audit to start the audit.

Community Additions