Share via

  • Article

Creating and Managing Audits with Transact-SQL

Using Management Studio or Transact-SQL to execute Transact-SQL Data Definition Language (DDL) statements, dynamic management views and functions, or catalog views, you can create simple or complex SQL Server Audit solutions for your SQL Server environment.

Reference Topics

You can use DDL statements, dynamic management views and functions, and catalog views to implement all aspects of SQL Server Audit.

Permissions

Each feature and command for SQL Server Audit has individual permission requirements.

To create, alter, or drop a Server Audit or Server Audit Specification, server principals require the ALTER ANY SERVER AUDIT or the CONTROL SERVER permission. To create, alter, or drop a Database Audit Specification, database principals require the ALTER ANY DATABASE AUDIT permission or the ALTER or CONTROL permission on the database. In addition, principals must have permission to connect to the database, or ALTER ANY SERVER AUDIT or CONTROL SERVER permissions.

Unless otherwise specified, viewing catalog views requires a principal to have one of the following:

  • Membership in the sysadmin fixed server role.

  • The CONTROL SERVER permission.

  • The VIEW SERVER STATE permission.

  • The ALTER ANY AUDIT permission.

  • The VIEW AUDIT STATE permission (gives only the principal access to the sys.server_audits catalog view).

A principal must have the VIEW SERVER STATE or ALTER ANY AUDIT permission to use the Dynamic Management Views.

For more information about how to grant rights and permissions, see GRANT (Transact-SQL).

Warning

Principals in the sysadmin role can tamper with any audit component and those in the db_owner role can tamper with audit specifications in a database. SQL Server Audit will validate that a logon that creates or alters an audit specification has at least the ALTER ANY DATABASE AUDIT permission. However, it does no validation when you attach a database. You should assume all Database Audit Specifications are only as trustworthy as those principals in the sysadmin or db_owner role.

Data Definition Language Statements

You can use the following DDL statements to create, alter, and drop audit specifications:

ALTER AUTHORIZATION

CREATE SERVER AUDIT

ALTER DATABASE AUDIT SPECIFICATION

CREATE SERVER AUDIT SPECIFICATION

ALTER SERVER AUDIT

DROP DATABASE AUDIT SPECIFICATION

ALTER SERVER AUDIT SPECIFICATION

DROP SERVER AUDIT

CREATE DATABASE AUDIT SPECIFICATION

DROP SERVER AUDIT SPECIFICATION

Dynamic Views and Functions

The following table lists the dynamic views and function that you can use for SQL Server Auditing.

Dynamic views and functions

Description

sys.dm_audit_actions

Returns a row for every audit action that can be reported in the audit log and every audit action group that can be configured as part of SQL Server Audit.

sys.dm_server_audit_status

Provides information about the current state of the audit.

sys.dm_audit_class_type_map

Returns a table that maps the class_type field in the audit log to the class_desc field in sys.dm_audit_actions.

fn_get_audit_file

Returns information from an audit file created by a server audit.

Catalog Views

The following table lists the catalog views that you can use for SQL Server auditing.

Catalog views

Description

sys.database_ audit_specifications

Contains information about the database audit specifications in a SQL Server audit on a server instance.

sys.database_audit_specification_details

Contains information about the database audit specifications in a SQL Server audit on a server instance for all databases.

sys.server_audits

Contains one row for each SQL Server audit in a server instance.

sys.server_audit_specifications

Contains information about the server audit specifications in a SQL Server audit on a server instance.

sys.server_audit_specifications_details

Contains information about the server audit specification details (actions) in a SQL Server audit on a server instance.

sys.server_file_audits

Contains stores extended information about the file audit type in a SQL Server audit on a server instance.