CREATE DATABASE AUDIT SPECIFICATION (Transact-SQL)

Syntax

CREATE DATABASE AUDIT SPECIFICATION audit_specification_name
{
    FOR SERVER AUDIT audit_name 
        [ { ADD ( { <audit_action_specification> | audit_action_group_name } ) 
      } [, ...n] ]
    [ WITH ( STATE = { ON | OFF } ) ]
}
[ ; ]
<audit_action_specification>::=
{
      action [ ,...n ]ON [ class :: ] securable BY principal [ ,...n ]
}

Arguments

• audit_specification_name
  Is the name of the audit specification.

• audit_name
  Is the name of the audit to which this specification is applied.

• audit_action_specification
  Is the specification of actions on securables by principals that should be recorded in the audit.

• action
  Is the name of one or more database-level auditable actions. For a list of audit actions, see SQL Server Audit Action Groups and Actions.

• audit_action_group_name
  Is the name of one or more groups of database-level auditable actions. For a list of audit action groups, see SQL Server Audit Action Groups and Actions.

• class
  Is the class name (if applicable) on the securable.

• securable
  Is the table, view, or other securable object in the database on which to apply the audit action or audit action group. For more information, see Securables.

• principal
  Is the name of SQL Server principal on which to apply the audit action or audit action group. For more information, see Principals (Database Engine).

• WITH ( STATE = { ON | OFF } )
  Enables or disables the audit from collecting records for this audit specification.

Remarks

Database audit specifications are non-securable objects that reside in a given database. When a database audit specification is created, it is in a disabled state.

Permissions

Users with the ALTER ANY DATABASE AUDITpermission can create database audit specifications and bind them to any audit.

After a database audit specification is created, it can be viewed by principals with the CONTROL SERVER,ALTER ANY DATABASE AUDITpermissions, or the sysadmin account.

Examples

The following example creates a server audit called Payrole_Security_Audit and then a database audit specification called Payrole_Security_Audit that audits SELECT and INSERT statements by the dbo user, for the HumanResources.EmployeePayHistory table in the AdventureWorks2008R2 database.

USE master ;
GO
-- Create the server audit.
CREATE SERVER AUDIT Payrole_Security_Audit
    TO FILE ( FILEPATH = 
'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA' ) ;
GO
-- Enable the server audit.
ALTER SERVER AUDIT Payrole_Security_Audit 
WITH (STATE = ON) ;
GO
-- Move to the target database.
USE AdventureWorks2008R2 ;
GO
-- Create the database audit specification.
CREATE DATABASE AUDIT SPECIFICATION Audit_Pay_Tables
FOR SERVER AUDIT Payrole_Security_Audit
ADD (SELECT , INSERT
     ON HumanResources.EmployeePayHistory BY dbo )
WITH (STATE = ON) ;
GO

See Also

Reference

Concepts