SQL Server 2012 : SQL Server Audit (part 3) - Viewing Audited Events,Querying Audit Catalog Views

1/22/2014 12:44:34 AM

6. Viewing Audited Events

After you enable your audit objects and audit specifications, SQL Server takes it from there. Audits for each monitored event declared in your audit specifications are recorded automatically to the destinations you’ve specified in your audit objects. After accumulating several audits, you’ll want to view them, of course.

Audits recorded to the event log can be examined using the Event Viewer (available from Administrative Tools in Control Panel). For example, Figure 1 shows the properties of an event recorded by a database audit for a DELETE statement against the TestTable table displayed using the Event Viewer.

Displaying an audit recorded to the Application event log using Event Viewer.

Figure 1. Displaying an audit recorded to the Application event log using Event Viewer.

Audits recorded to the file system are not stored in plain text files that can simply be viewed in Notepad. Instead, they are binary files that you can view in one of two ways. One way is from inside SQL Server Management Studio. Right-click the desired audit object beneath the Security node at the server instance level (not the Security node at the database level) in the Object Explorer, and then choose View Audit Logs. This opens the Log File Viewer window, as shown in Figure 2.

Each audit entry contains a wealth of detailed information about the event that was captured and recorded. This includes date and time stamp, server instance, action, object type, success or failure, permissions, principal name and ID (that is, the user that performed the audited action), database name, schema name, object name, the actual statement that was executed (or attempted), and more.

Displaying audits recorded to the file system using the Log File Viewer in SSMS.

Figure 2. Displaying audits recorded to the file system using the Log File Viewer in SSMS.

Alternatively, you can use the table-valued function (TVF) named sys.fn_get_audit_file. This function accepts a parameter that points to one or more audit files (using wildcard pattern matching). Two additional parameters allow you to specify the initial file to process and a known offset location to start reading audit records from. (Both of these parameters are optional but must still be specified using the keyword default.) The function then reads the binary data from the file(s) and formats the audit entries into an ordinary table that gets returned back, as Example 9 demonstrates.

Example 9. Querying audit files.

event_time, database_name, object_name, statement
sys.fn_get_audit_file('C:\Demo\SqlAudits\*.sqlaudit', default, default)

Here are some abbreviated results from this query:

event_time              database_name object_name statement
----------------------- ------------- ----------- ----------------------------------------
2012-01-05 19:33:19.381 MyDB CREATE TABLE TestTable(TestId int PRIM..
2012-01-05 19:33:45.789 MyDB TestTable INSERT INTO TestTable VALUES(1, 'Test'..
2012-01-05 19:33:45.789 MyDB TestTable SELECT * FROM TestTable
2012-01-05 19:33:45.789 MyDB TestTable DELETE FROM TestTable WHERE TestId=1

You can easily filter and sort this data using WHERE and ORDER BY, as well as INSERT the data elsewhere. The sys.fn_get_audit_file function represents an advantage that auditing to the file system has over auditing to the Windows event log, as there is no equivalent function provided for querying and manipulating audits in the event logs.

7. Querying Audit Catalog Views

SQL Server provides a number of catalog views that you can query for information about the audits and audit specifications running on any server instance. These audit catalog views are listed in Table 1, accompanied by a brief description of each.

Table 1. Audit catalog views.

Catalog View Name



Returns all of the audit objects that are recorded to the file system.


Returns all of the server-level audit specifications.


Returns detailed, monitored event information for all of the server-level audit specifications.


Returns all of the database-level audit specifications.


Returns detailed, monitored event information for all of the database-level audit specifications.


Returns the status of each audit object.


Returns every audit action that can be reported on and every audit action group that can be configured.


Returns a table that maps the class_type field in the audit log to the class_desc field in sys.dm_audit_actions.

  •  SQL Server 2012 : Encryption Support (part 4) - Transparent Data Encryption - Enabling TDE, Backing Up the Certificate
  •  SQL Server 2012 : Encryption Support (part 3) - Transparent Data Encryption - Creating Keys and Certificates for TDE
  •  SQL Server 2012 : Encryption Support (part 2) - Encrypting Data at Rest
  •  SQL Server 2012 : Encryption Support (part 1) - Encrypting Data on the Move
  •  SQL Server 2012 : Authentication and Authorization (part 2) - User-Schema Separation,Execution Context
  •  SQL Server 2012 : Authentication and Authorization (part 1) - How Clients Establish a Connection, Password Policies
  •  SQL Server 2012 : SQL Server Security Overview
  •  SQL Server 2012 : Working with Transactions - Transactions in SQL CLR (CLR Integration)
  •  SQL Server 2012 : Distributed Transactions (part 4) - Using a Resource Manager in a Successful Transaction
  •  SQL Server 2012 : Distributed Transactions (part 3) - Distributed Transactions in the .NET Framework - Writing Your Own Resource Manager
    Video tutorials
    - How To Install Windows 8 On VMware Workstation 9

    - How To Install Windows 8

    - How To Install Windows Server 2012

    - How To Disable Windows 8 Metro UI

    - How To Change Account Picture In Windows 8

    - How To Unlock Administrator Account in Windows 8

    - How To Restart, Log Off And Shutdown Windows 8

    - How To Login To Skype Using A Microsoft Account

    - How To Enable Aero Glass Effect In Windows 8

    - How To Disable Windows Update in Windows 8

    - How To Disable Windows 8 Metro UI

    - How To Add Widgets To Windows 8 Lock Screen
    programming4us programming4us
    Top 10
    Free Mobile And Desktop Apps For Accessing Restricted Websites
    MASERATI QUATTROPORTE; DIESEL : Lure of Italian limos
    TOYOTA CAMRY 2; 2.5 : Camry now more comely
    KIA SORENTO 2.2CRDi : Fuel-sipping slugger
    How To Setup, Password Protect & Encrypt Wireless Internet Connection
    Emulate And Run iPad Apps On Windows, Mac OS X & Linux With iPadian
    Backup & Restore Game Progress From Any Game With SaveGameProgress
    Generate A Facebook Timeline Cover Using A Free App
    New App for Women ‘Remix’ Offers Fashion Advice & Style Tips
    SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th