Create a server audit and database audit specification

Applies to: yesSQL Server (all supported versions)

This article describes how to create a server audit and a database audit specification in SQL Server 2019 (15.x) 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-level 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).

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's in a disabled state.

When you're creating or modifying a database audit specification in a user database, don't include audit actions on server-scope objects, like the system views. If you include server-scoped objects, the audit will be created. But the server-scoped objects won't be included, and no error will return. To audit server-scoped objects, use a database audit specification in the master database.

Database audit specifications reside in the database where they're created, except for the TempDB system database.

Security

Permissions

  • Users who have the ALTER ANY DATABASE AUDIT permission can create database audit specifications and bind them to any audit.

  • After a database audit specification is created, principals who have CONTROL SERVER or ALTER ANY DATABASE AUDIT permissions can view it. The sysadmin account can also view it.

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 finish selecting options, select OK.

To create a database-level audit specification

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

  2. Expand the Security folder.

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

    These options are available in the Create Database Audit Specification dialog box:

    Name
    The name of the database audit specification. A name is generated automatically when you create a server audit specification. The name is editable.

    Audit
    The name of an existing server audit object. 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 a list of database-level audit action groups and audit actions and descriptions 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 option is available only for audit actions. It doesn't apply to audit groups.

    Ellipsis (...)
    Opens the Select Objects dialog box so you can 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 box so you can browse for and select an available object, based on the specified Object Name.

  4. When you finish selecting options, select OK.

Using Transact-SQL

To create a server audit

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

  2. On the Standard bar, select New Query.

  3. Paste the following example into the query window and then select Execute.

    USE master ;  
    GO  
    -- Create the server audit.   
    CREATE SERVER AUDIT Payrole_Security_Audit  
        TO FILE ( FILEPATH =   
    'C:\Program Files\Microsoft SQL Server\MSSQL13.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, select New Query.

  3. Paste the following example into the query window and then select Execute. This example creates a database audit specification called Audit_Pay_Tables. It audits SELECT and INSERT statements by the dbo user for the HumanResources.EmployeePayHistory table, based on the server audit defined in the previous section.

    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).