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.
You can use DDL statements, dynamic management views and functions, and catalog views to implement all aspects of SQL Server Audit.
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).
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:
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
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.
Provides information about the current state of the audit.
Returns a table that maps the class_type field in the audit log to the class_desc field in sys.dm_audit_actions.
Returns information from an audit file created by a server audit.
The following table lists the catalog views that you can use for SQL Server auditing.
Contains information about the database audit specifications in a SQL Server audit on a server instance.
Contains information about the database audit specifications in a SQL Server audit on a server instance for all databases.
Contains one row for each SQL Server audit in a server instance.
Contains information about the server audit specifications in a SQL Server audit on a server instance.
Contains information about the server audit specification details (actions) in a SQL Server audit on a server instance.
Contains stores extended information about the file audit type in a SQL Server audit on a server instance.