SQL Server Audit Records

The SQL Server Audit feature enables you to audit server-level and database-level groups of events and events. For more information, see Understanding SQL Server Audit. 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

char(4)

Yes

succeeded

Indicates if the action that triggered the event succeeded

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 includes:

  • 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.

char(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 includes:

  • server objects

  • databases

  • database objects

  • schema objects

  • TSQL 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.

See Also

Reference

Concepts