Share via

  • Article

How to: Create a Server Audit and Database Audit Specification

Before you can create a database 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 "ServerAuditDB" that sends the audit output to the Windows Application event log. This specification is then used to create a database-level audit for the AdventureWorks2008R2 database. This database audit will write an event to the audit log whenever the database owner (dbo) for the specified table does a SELECT or INSERT on that table.

Procedures

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 ServerAuditDB.

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

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

Create a database-level audit specification

  1. In Object Explorer, recursively expand the Databases node down to and including the Security node for the AdventureWorks2008R2 database.

  2. Right-click Database Audit Specifications and then click New Database Audit Specification. This opens the Create Database Audit Specification page.

  3. In the Name field, type AdventureworksDBAudit1.

  4. For Server Audit, pick ServerAuditDB from the list.

  5. In the table grid, click the row that is prefixed by an asterisk (*). For the Audit Action Type, pick SELECT from the list.

  6. For Object Name, open the Select Objects page.

  7. Click Browse to open the Browse for Objects page. Use this browser to locate and select the Sales.Customer table in AdventureWorks2008R2. Click OK until you return to the Create Database Audit Specification page. Note that the Object Schema information is automatically provided based on your selection.

  8. Repeat step 7 to browse for and select a Principal Name. Use the browser to pick the dbo as the principal.

  9. In the table grid, click the row that is prefixed by an asterisk (*) and add a second audit action. For Audit Action Type, pick INSERT from the list. Use the same parameters and steps (6 - 8) to configure the Object Name and Principal Name.

  10. Click OK to save the database audit specification.

  11. Expand the Audits node and right-click ServerAuditDB. Click Enable Audit to start the audit.