Chapter 61. SQL Audit


  • Configuring SQL Audit

  • Tracking server events

At one of the pre-Katmai (the code name for SQL Server 2008 while it was being developed) NDA (non-disclosure agreement—that is, secret) sessions for MVPs, the SQL Server team asked how many of us would like an easy way to audit selects. Nearly every MVP's hand went up. The SQL Server community has wanted a more powerful auditing mechanism for a long time.

SQL Audit is the answer.

Based on the new Extended Events technology, SQL Audit is both lightweight and powerful. While it's possible to "roll your own" auditing solution from Extended Events, SQL Audit is an out-of-the-box solution to leverage Extended Events and collect server and database events. It's blazingly fast, easy to configure, and cool.

While Extended Events is available for all editions of SQL Server, SQL Audit is available only for Enterprise (and Developer) Edition.

SQL Audit Technology Overview

It takes several SQL Audit components working together to create a functioning Audit. A SQL Server Audit object is a bucket that collects the audit events defined by a Server Audit Specification and the Database Audit Specification, and sends the audited events to a target. Here are the facts:

  • A SQL Server Audit object can be written to by one Server Audit Specification and one Database Audit Specification per database.

  • A SQL Server Audit can belong to only one SQL Server instance, but there may be several SQL Server Audits within an instance.

  • A Server ...

Get Microsoft® SQL Server® 2008 Bible now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.