Share via

CREATE DATABASE AUDIT SPECIFICATION (Transact-SQL)

  • Article

Creates a database audit specification object using the SQL Server audit feature. For more information, see SQL Server Audit (Database Engine).

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.

Permissions

Users with the ALTER ANY DATABASE AUDIT permission 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 AUDIT permissions, 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 AdventureWorks2012 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 AdventureWorks2012 ;
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

CREATE SERVER AUDIT (Transact-SQL)

ALTER SERVER AUDIT (Transact-SQL)

DROP SERVER AUDIT (Transact-SQL)

CREATE SERVER AUDIT SPECIFICATION (Transact-SQL)

ALTER SERVER AUDIT SPECIFICATION (Transact-SQL)

DROP SERVER AUDIT SPECIFICATION (Transact-SQL)

CREATE DATABASE AUDIT SPECIFICATION (Transact-SQL)

ALTER DATABASE AUDIT SPECIFICATION (Transact-SQL)

DROP DATABASE AUDIT SPECIFICATION (Transact-SQL)

ALTER AUTHORIZATION (Transact-SQL)

sys.fn_get_audit_file (Transact-SQL)

sys.server_audits (Transact-SQL)

sys.server_file_audits (Transact-SQL)

sys.server_audit_specifications (Transact-SQL)

sys.server_audit_specification_details (Transact-SQL)

sys.database_audit_specifications (Transact-SQL)

sys.database_audit_specification_details (Transact-SQL)

sys.dm_server_audit_status (Transact-SQL)

sys.dm_audit_actions (Transact-SQL)

Concepts

Create a Server Audit and Server Audit Specification