1 out of 3 rated this helpful - Rate this topic

CREATE DATABASE AUDIT SPECIFICATION (Transact-SQL)

SQL Server 2008

Creates a database audit specification object using the SQL Server audit feature. For more information, see Understanding SQL Server Audit.

Topic link icon Transact-SQL Syntax Conventions

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.

For information about the database audit specifications in a SQL Server audit, use the sys.database_ audit_specifications catalog view.

When you are creating or modifying a database audit specification in a user database, do not include audit actions on server-scope objects, such as the system views. If server-scoped objects are included, the audit will be created. However, the server-scoped objects will not be included, and no error will be returned. To audit server-scope objects, use a database audit specification in the master database.

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 AdventureWorks database.

USE master ;
GO
-- Create the server audit.
CREATE SERVER AUDIT Payrole_Security_Audit
    TO FILE ( FILEPATH = 
'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA' ) ;
GO
-- Enable the server audit.
ALTER SERVER AUDIT Payrole_Security_Audit 
WITH (STATE = ON) ;
GO
-- Move to the target database.
USE AdventureWorks ;
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