SQL Server Audit Records

Άρθρο
02/28/2023

Applies to: SQL Server

The SQL Server 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). SQL Server.

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

Remarks

Some actions do not populate a column's value because it might be non-applicable to the action.

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