Microsoft® SQL Server® 2012 Bible

971

Chapter 42: SQL Audit

42

Enabling/Disabling the Audit

Object Explorer’s SQL Server Audit node visually indicates whether the Audit is enabled or

disabled with a red mark on the node if the item is currently turned off. The context menu

includes commands to enable or disable the Audit.

Using T-SQL, the alter command has an additional parameter that enables or disables the

SQL Server Audit. The following command enables the SQL Server 2012 Bible Audit:

ALTER SERVER AUDIT [SQL Server 2012 Bible Audit]

WITH (State = ON)

Server Audit Specifi cations

A new Server Audit Specifi cation may be created from Object Explorer using the

Security ➪ Server Audit Specifi cations’ context menu ➪ New Server Audit Specifi cation

command, which opens the Create Server Audit Specifi cation dialog, as shown in

Figure 42-2.

Each SQL Server Audit object may have only one Server Audit Specifi cation, but there may

be multiple Server Audits running, and each may have a Server Audit Specifi cation.

The new Server Audit Specifi cation can’t be created unless it points to an existing SQL

Server Audit object and that SQL Server Audit object currently does not have a Server Audit

Specifi cation connected to it.

With SQL Server 2012 Server-level audits are now able to be created on all editions of SQL

Server.

Adding Actions

The most important part of defi ning the Server Audit Specifi cation is adding actions to the

specifi cation. These actions aren’t in a hierarchy like the DDL Triggers events and groups;

each action group must be added individually.

Beginning with 2012, SQL Server audit specifi cations support a user defi ned audit group.

You can use the sp_audit_write stored procedure to record audited events to the audit

log. By using user-defi ned audit events, developers can code their applications to write cus-

tom information to the audit log.

The server-related events that can be audited are organized into 35 action groups. (Most are

shown in the drop-down list in Figure 42-2.) Potentially, a Server Audit Specifi cation could

have all 35 action groups.

The Server Audit State Change Audit group, which audits whether SQL Audit is enabled or

disabled, is automatically audited.

c42.indd 971c42.indd 971 7/31/2012 10:17:48 AM7/31/2012 10:17:48 AM

http://www.it-ebooks.info