Database Audit Specification

A Database Audit Specification defines which Audit Action Groups will be audited for the specific database in which the specification is created. Some audit action groups comprise server level actions like the creation of a database or modification of a server role and hence are not available in database audit specifications. Such action groups must be defined in a Server Audit Specification.

Besides selecting action groups you must give the server audit specification a name and choose which Audit object it will be associated. Events generated by this audit specification will be logged according to the options in the associated Audit. In other words if you add the FAILED_LOGIN_GROUP to your server audit specification and then it assign it to an Audit that defines \\server\share as the file destination, the failed login events will be written to log files at that destination along with events from any other audit specifications tied to that audit object.

After creating a server audit specification you must enable it before SQL Server will begin logging events indicated by the action groups you selected. Before you can modify an audit specification you must disable it. No more events will be generated by the specification until you reenable it.

Even though you see columns in the dialog below for object and principal information, they are not used for action group rules; the action groups you choose are audited for all applicable objects and regardless of who performs the operation. Only audit action rules specified for certain commands are scoped by object and principal.

Auditing Tables and Stored Procedures

You may notice that there are no action groups for row level commands like select, insert, update or delete nor any action groups for tracking the execution of stored procedures. This is possible with Audit Actions.

Maintaining Database Audit Specifications

SQL Server Management Studio provides full GUI access to administering database audit specifications but you may also use these Transact-SQL commands:

  • CREATE DATABASE AUDIT SPECIFICATION
  • ALTER DATABASE AUDIT SPECIFICATION
  • DROP DATABASE AUDIT SPECIFICATION

LOGbinder provides a free tool to help you implement server-level audit policy through a step-by-step interface: SQL Audit Policy Wizard.

 

Upcoming Webinars
    Additional Resources