| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | helpviewer_keywords | |
|---|---|---|---|---|---|---|---|---|---|
SQL Server Audit Records |
SQL Server audits consist of audit action items, which are recorded to an audit target. Check this summary for the records that can be sent to a target. |
sravanisaluru |
srsaluru |
03/23/2022 |
sql |
security |
conceptual |
|
[!INCLUDE SQL Server] The [!INCLUDEssNoVersion] Audit feature enables you to audit server-level and database-level groups of events and events. For more information, see SQL Server Audit (Database Engine). [!INCLUDEssNoVersion].
Audits consist of zero or more audit action items, which are recorded to an audit target. The audit target can be a binary file, the Windows Application event log, or the Windows Security event log. The records sent to the target can contain the elements described in the following table:
| Column name | Description | Type | Always available |
|---|---|---|---|
| event_time | Date/time when the auditable action is fired. | datetime2 | Yes |
| sequence_no | Tracks the sequence of records within a single audit record that was too large to fit in the write buffer for audits. | int | Yes |
| action_id | ID of the action Tip: To use action_id as a predicate it must be converted from a character string to a numeric value. For more information, see Filter SQL Server Audit on action_id / class_type predicate. |
varchar(4) | Yes |
| succeeded | Indicates whether or not the permission check of the action triggering the audit event succeeded or failed. | bit - 1 = Success, 0 = Fail |
Yes |
| permission_bitmask | When applicable, shows the permissions that were granted, denied, or revoked | bigint | No |
| is_column_permission | Flag indicating a column level permission | bit - 1 = True, 0 = False |
No |
| session_id | ID of the session on which the event occurred. | int | Yes |
| server_principal_id | ID of the login context that the action is performed in. | int | Yes |
| database_principal_id | ID of the database user context that the action is performed in. | int | No |
| object_id | The primary ID of the entity on which the audit occurred. This ID can be: server objects databases database objects schema objects |
int | No |
| target_server_principal_id | Server principal that the auditable action applies to. | int | Yes |
| target_database_principal_id | Database principal that the auditable action applies to. | int | No |
| class_type | Type of auditable entity that the audit occurs on. | varchar(2) | Yes |
| session_server_principal_name | Server principal for the session. | sysname | Yes |
| server_principal_name | Current login. | sysname | Yes |
| server_principal_sid | Current login SID. | varbinary | Yes |
| database_principal_name | Current user. | sysname | No |
| target_server_principal_name | Target login of the action. | sysname | No |
| target_server_principal_sid | SID of the target login. | varbinary | No |
| target_database_principal_name | Target user of the action. | sysname | No |
| server_instance_name | Name of the server instance where the audit occurred. Uses the standard machine\instance format. | nvarchar(120) | Yes |
| database_name | The database context in which the action occurred. | sysname | No |
| schema_name | The schema context in which the action occurred. | sysname | No |
| object_name | The name of the entity on which the audit occurred. This name can be: server objects databases database objects schema objects Transact-SQL statement (if any) |
sysname | No |
| statement | TSQL statement (if any) | nvarchar(4000) | No |
| additional_information | Any additional information about the event, stored as XML. | nvarchar(4000) | No |
Some actions do not populate a column's value because it might be non-applicable to the action.
[!INCLUDEssNoVersion] Audit stores 4000 characters of data for character fields in an audit record. When the additional_information and statement values returned from an auditable action return more than 4000 characters, the sequence_no column is used to write multiple records into the audit report for a single audit action to record this data. The process is as follows:
-
The statement column is divided into 4000 characters.
-
[!INCLUDEssNoVersion] Audit writes as the first row for the audit record with the partial data. All the other fields are duplicated in each row.
-
The sequence_no value is incremented.
-
This process is repeated until all the data is recorded.
You can connect the data by reading the rows sequentially using the sequence_no value, and the event_Time, action_id and session_id columns to identify the action.
CREATE SERVER AUDIT (Transact-SQL)
ALTER SERVER AUDIT (Transact-SQL)
DROP SERVER AUDIT (Transact-SQL)
CREATE SERVER AUDIT SPECIFICATION (Transact-SQL)
ALTER SERVER AUDIT SPECIFICATION (Transact-SQL)
DROP SERVER AUDIT SPECIFICATION (Transact-SQL)
CREATE DATABASE AUDIT SPECIFICATION (Transact-SQL)
ALTER DATABASE AUDIT SPECIFICATION (Transact-SQL)
DROP DATABASE AUDIT SPECIFICATION (Transact-SQL)
ALTER AUTHORIZATION (Transact-SQL)
sys.fn_get_audit_file (Transact-SQL)
sys.server_audits (Transact-SQL)
sys.server_file_audits (Transact-SQL)
sys.server_audit_specifications (Transact-SQL)
sys.server_audit_specification_details (Transact-SQL)
sys.database_audit_specifications (Transact-SQL)
sys.database_audit_specification_details (Transact-SQL)
sys.dm_server_audit_status (Transact-SQL)