Create a server audit and database audit specification
Applies to:
SQL Server
This article describes how to create a server audit and a database audit specification in SQL Server 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
In Object Explorer, expand the Security folder.
Right-click the Audits folder and select New Audit. For more information, see Create a server audit and server audit specification.
When you finish selecting options, select OK.
To create a database-level audit specification
In Object Explorer, expand the database where you want to create the audit specification.
Expand the Security folder.
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.When you finish selecting options, select OK.
Using Transact-SQL
To create a server audit
In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, select New Query.
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
In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, select New Query.
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 thedbo
user for theHumanResources.EmployeePayHistory
table, based on the server audit defined in the previous section.USE AdventureWorks2022; 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).