Create a Server Audit and Database Audit Specification

This topic describes how to create a server audit and database audit specification in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL.

Auditing an instance of SQL Server or a SQL Server database involves tracking and logging events that occur on the system. The SQL Server Audit object collects a single instance of server- or database-level actions and groups of actions to monitor. The audit is at the SQL Server instance level. You can have multiple audits per SQL Server instance. The Database-Level Audit Specification object belongs to an audit. You can create one database audit specification per SQL Server database per audit. For more information, see SQL Server Audit (Database Engine).

In This Topic

• Before you begin:

  Limitations and Restrictions

  Security

• To create a server audit and database audit specification, using:

  SQL Server Management Studio

  Transact-SQL

Before You Begin

Limitations and Restrictions

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.

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.

Database audit specifications reside in the database where they are created, with the exception of the tempdb system database.

Security

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.

[Top]

Using SQL Server Management Studio

To create a server audit

1. In Object Explorer, expand the Security folder.

2. Right-click the Audits folder and select New Audit…. For more information, see Create a Server Audit and Server Audit Specification.

3. When you are finished selecting options, click OK.

To create a database-level audit specification

1. In Object Explorer, expand the database where you want to create an audit specification.

2. Expand the Security folder.

3. Right-click the Database Audit Specifications folder and select New Database Audit Specification….

   The following options are available on the Create Database Audit Specification dialog box.

   • Name
     The name of the database audit specification. This is generated automatically when you create a new server audit specification but is editable.

   • Audit
     The name of an existing database audit. Either type in the name of the audit or select it from the list.

   • Audit Action Type
     Specifies the database-level audit action groups and audit actions to capture. For the list of database-level audit action groups and audit actions and a description of the events they contain, see SQL Server Audit Action Groups and Actions.

   • Object Schema
     Displays the schema for the specified Object Name.

   • Object Name
     The name of the object to audit. This is only available for audit actions; it does not apply to audit groups.

   • Ellipsis (…)
     Opens the Select Objects dialog to browse for and select an available object, based on the specified Audit Action Type.

   • Principal Name
     The account to filter the audit by for the object being audited.

   • Ellipsis (…)
     Opens the Select Objects dialog to browse for and select an available object, based on the specified Object Name.

4. When you are finished selecting option, click OK.

[Top]

Using Transact-SQL

To create a server audit

1. In Object Explorer, connect to an instance of Database Engine.

2. On the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute.

   USE master ;
   GO
   -- Create the server audit. 
   CREATE SERVER AUDIT Payrole_Security_Audit
       TO FILE ( FILEPATH = 
   'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA' ) ; 
   GO
   -- Enable the server audit. 
   ALTER SERVER AUDIT Payrole_Security_Audit 
   WITH (STATE = ON) ;
   

To create a database-level audit specification

1. In Object Explorer, connect to an instance of Database Engine.

2. On the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute. The example creates a database audit specification called Audit_Pay_Tables that audits SELECT and INSERT statements by the dbo user, for the HumanResources.EmployeePayHistory table based on the server audit defined above.

   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
   

For more information, see CREATE SERVER AUDIT (Transact-SQL) and CREATE DATABASE AUDIT SPECIFICATION (Transact-SQL).

[Top]