Microsoft SQL Server 2012 Bible

Creating an Audit

The first step to working with SQL Audit is to create a SQL Server Audit object.

In Object Explorer, SQL Server Audit objects are listed under the server → Security → Audits node. The New Audit command in the Audits node context menu opens the Create Audit dialog, as shown in Figure 42.1.

Figure 42.1 Use the Create Audit dialog to define SLQ Server Audit objects, which collect events defined by the Server Audit Specification or the Database Audit Specification.

The queue delay, which determines how long SQL Server can wait before processing the Extended Event, ranges from 1 second (1,000 milliseconds) to almost 25 days (2,147,483,647 milliseconds). The default (1 second) is reasonable for most situations. If the server is hit with heavy traffic, increasing the queue delay gives SQL Audit more flexibility.

Selecting Shut Down Server on Fail Operation ensures that the target file or the log receiving the events can be written to. If SQL Audit can't write to the target, then it writes a msg_audit_forced_shutdown event to the error log and shuts down the server. SQL Server Audit is much more resilient to failures in SQL Server 2012; if the target file or log receiving the events cannot be written to, SQL Server Audit can recover after the file or log comes back online. A new option has also been added, which enables you to fail database actions if they cause audited ...