Export (0) Print
Expand All

sys.fn_get_audit_file (Transact-SQL)

Returns information from an audit file created by a server audit. For more information, see Understanding SQL Server Audit.

Topic link icon Transact-SQL Syntax Conventions


fn_get_audit_file ( file_pattern, {default | initial_file_name | NULL }, {default | audit_file_offset | NULL } )

file_pattern

Specifies the directory or path and file name for the audit file set to be read. This argument must include both a path (drive letter or network share) and a file name that can include a wildcard. A single asterisk (*) can be used to collect multiple files from an audit file set. For example:

  • <path>\* - Collect all audit files in the specified location.

  • <path>\LoginsAudit_{GUID} - Collect all audit files that have the specified name and GUID pair.

  • <path>\LoginsAudit_{GUID}_00_29384.sqlaudit - Collect a specific audit file.

NoteNote

Passing a path without a file name pattern will generate an error.

initial_file_name

Specifies the path and name of a specific file in the audit file set to start reading audit records from.

NoteNote

The initial_file_name argument must contain valid entries or must contain either the default | NULL value.

audit_file_offset

Specifies a known location with the file specified for the initial_file_name. When this argument is used the function will start reading at the first record of the Buffer immediately following the specified offset.

NoteNote

The audit_file_offset argument must contain valid entries or must contain either the default | NULL value.

The following table describes the audit file content that can be returned by this function.

Column name

Type

Description

event_time

datetime2

Date and time when the auditable action is fired. Is not nullable.

sequence_number

int

Tracks the sequence of records within a single audit record that was too large to fit in the write buffer for audits. Is not nullable.

action_id

char(4)

ID of the action. Is not nullable.

succeeded

bit

1 = success

0 = fail

Indicates whether the action that triggered the event succeeded. Is not nullable. For all events other than login events, this only reports whether the permission check succeeded or failed, not the operation.

permission_bitmask

bigint

In some actions, this is the permissions that were grant, denied, or revoked.

is_column_permission

bit

1 = true

0 = false

Flag indicating if this is a column level permission. Is not nullable. Returns 0 when the permission_bitmask = 0.

session_id

int

ID of the session on which the event occurred. Is not nullable.

server_principal_id

int

ID of the login context that the action is performed in. Is not nullable.

database_principal_id

int

ID of the database user context that the action is performed in. Is not nullable. Returns 0 if this does not apply. For example, a server operation.

target_server_principal_id

int

Server principal that the GRANT/DENY/REVOKE operation is performed on. Is not nullable. Returns 0 if not applicable.

target_database_principal_id

int

The database principal the GRANT/DENY/REVOKE operation is performed on. Is not nullable. Returns 0 if not applicable.

object_id

int

The ID of the entity on which the audit occurred. This includes the following:

  • Server objects

  • Databases

  • Database objects

  • Schema objects

Is not nullable. Returns 0 if the entity is the Server itself or if the audit is not performed at an object level. For example, Authentication.

class_type

char(2)

The type of auditable entity that the audit occurs on. Is not nullable.

session_server_principal_name

sysname

Server principal for session. Is nullable.

server_principal_name

sysname

Current login. Is nullable.

server_principal_sid

varbinary

Current login SID. Is nullable.

database_principal_name

sysname

Current user. Is nullable. Returns NULL if not available.

target_server_principal_name

sysname

Target login of action. Is nullable. Returns NULL if not applicable.

target_server_principal_sid

varbinary

SID of target login. Is nullable. Returns NULL if not applicable.

target_database_principal_name

sysname

Target user of action. Is nullable. Returns NULL if not applicable.

server_instance_name

nvarchar(120)

Name of the server instance where the audit occurred. The standard server\instance format is used.

database_name

sysname

The database context in which the action occurred. Is nullable. Returns NULL for audits occuring at the server level.

schema_name

sysname

The schema context in which the action occurred. Is nullable. Returns NULL for audits occuring outside a schema.

object_name

sysname

The name of the entity on which the audit occurred. This includes the following:

  • Server objects

  • Databases

  • Database objects

  • Schema objects

Is nullable. Returns NULL if the entity is the Server itself or if the audit is not performed at an object level. For example, Authentication.

statement

nvarchar(4000)

TSQL statement if it exists. Is nullable. Returns NULL if not applicable.

additional_information

nvarchar(4000)

Unique information that only applies to a single event is returned as XML. A small number of auditable actions contain this kind of information.

Is nullable. Returns NULL when there is no additional information reported by the event.

file_name

varchar(260)

The path and name of the audit log file that the record came from. Is not nullable.

audit_file_offset

bigint

The buffer offset in the file that contains the audit record. Is not nullable.

If the file_pattern argument passed to fn_get_audit_file references a path or file that does not exist, or if the file is not an audit file, the MSG_INVALID_AUDIT_FILE error message is returned.

Requires the CONTROL SERVER permission.

This example reads from a file that is named \\serverName\Audit\HIPPA_AUDIT.sqlaudit.

SELECT * FROM sys.fn_get_audit_file ('\\serverName\Audit\HIPPA_AUDIT.sqlaudit',default,default);
GO

For a full example about how to create an audit, see Understanding SQL Server Audit.

Community Additions

ADD
Show:
© 2014 Microsoft