TechNet
Export (0) Print
Expand All

SQL Server Audit Records

 

Applies To: SQL Server 2016

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 nameDescriptionTypeAlways available
event_timeDate/time when the auditable action is fired.datetime2Yes
sequence_noTracks the sequence of records within a single audit record that was too large to fit in the write buffer for audits.intYes
action_idID 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
succeededIndicates if the action that triggered the event succeededbit – 1 = Success, 0 = FailYes
permission_bitmaskWhen applicable, shows the permissions that were granted, denied, or revokedbigintNo
is_column_permissionFlag indicating a column level permissionbit – 1 = True, 0 = FalseNo
session_idID of the session on which the event occurred.intYes
server_principal_idID of the login context that the action is performed in.intYes
database_principal_idID of the database user context that the action is performed in.intNo
object_ idThe primary ID of the entity on which the audit occurred. This includes:

server objects

databases

database objects

schema objects
intNo
target_server_principal_idServer principal that the auditable action applies to.intYes
target_database_principal_idDatabase principal that the auditable action applies to.intNo
class_typeType of auditable entity that the audit occurs on.varchar(2)Yes
session_server_principal_nameServer principal for the session.sysnameYes
server_principal_nameCurrent login.sysnameYes
server_principal_sidCurrent login SID.varbinaryYes
database_principal_nameCurrent user.sysnameNo
target_server_principal_nameTarget login of the action.sysnameNo
target_server_principal_sidSID of the target login.varbinaryNo
target_database_principal_nameTarget user of the action.sysnameNo
server_instance_nameName of the server instance where the audit occurred. Uses the standard machine\instance format.nvarchar(120)Yes
database_nameThe database context in which the action occurred.sysnameNo
schema_nameThe schema context in which the action occurred.sysnameNo
object_nameThe name of the entity on which the audit occurred. This includes:

server objects

databases

database objects

schema objects

TSQL statement (if any)
sysnameNo
statementTSQL statement (if any)nvarchar(4000)No
additional_informationAny 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.

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)

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)

sys.dm_audit_actions (Transact-SQL)

sys.dm_audit_class_type_map (Transact-SQL)

Show:
© 2016 Microsoft