SQL Server 2008 : Accessing Audit Details

Learn how to use the SQL Server 2008 catalog view and dynamic
management view to access the audit details of existing server and database
level audits.

Previous articles have illustrated the Auditing feature
of SQL Server, namely creating a server level audit, database level audit and
granular level database level audit. This article illustrates how to use the
SQL Server 2008 catalog view and dynamic management view to access the audit
details of existing server and database level audits.

The first and foremost information that any database administrator
needs to know is what audits are defined on the server. You can find that by
using the following catalog view. [Refer Fig 1.0]

select Name, is_state_enabled, type_desc from sys.server_audits

audits are defined on the server

Fig 1.0

The column Type_Desc gives you the target information
of the audit defined. Here we see that Compliance_Server_audit goes to the Windows
Application Log and the other two audit’s targets are pointing to a file.

You can also view this information from SQL Server
Management Studio under the Security\Audit folder as shown below. [Refer Fig
1.1]

SQL Server Management Studio

Fig 1.1

The next level of detail that a database administrator
should know is the location of the target, size and status of the audit itself.
You
can find that by using the following Dynamic management view. [Refer Fig 1.3]

select name, status_desc, audit_file_path, audit_file_size from 
sys.dm_server_audit_status 

From the result, you can see that the target for both
compliance_database_audit and MyDB_Database_Granular_Audit points to
C:\SQLAudit and the file size are shown as 5MB.

We can verify the location and size using windows
explorer as shown below. [Refer Fig 1.4]

This information can also be viewed from the SQL Server Management studio using Audit properties. [Refer Fig 1.5]

More detailed information about the auditing with FILE
as target can be found using the following catalog view. [Refer Fig 1.6]

select name, on_failure ,max_file_size ,max_rollover_files ,log_file_path 
,log_file_name  from  sys.server_file_audits 

catalog view

Fig 1.6

Details
of a database audit specification of an individual database audit can be
retrieved using the following two catalog views. [Refer Fig 1.7]

select * from sys.database_audit_specification_details where database_specification_id in
(select database_specification_id from sys.database_audit_specifications where name ='MyDB_Database_Granular_Audit_SPEC')

Details of a database audit specification of an individual database audit

Fig 1.7

The
information can be retrieved using SQL Server Management studio under database
MyDB\Security\Database Audit Specification. [Fig 1.8]

The information can be retrieved using SQL Server Management studio

Fig 1.8

You
can check the extended events
that are created by SQL server related to this audit using the following dynamic
management view command. [Refer Fig 1.9]

select s.name as [session name], se.event_name as [event name], 
          p.name as [package name], t.target_name as [target name]
from sys.dm_xe_sessions s 
join sys.dm_xe_session_events se on s.address = se.event_session_address
join sys.dm_xe_packages p on se.event_package_guid = p.guid 
join sys.dm_xe_session_targets t on s.address=t.event_session_address

the extended events that are created by SQL server related to this audit

Fig 1.9

There
are other metadata information about the audit that can be retrieved using dynamic
management views. They are;

  • sys.dm_audit_actions: This view contains information about every audit
    action that can be reported in the audit log and every audit action group that
    can be configured as part of SQL Server Audit.
  • sys.dm_audit_class_type_map: This dynamic management view maps the
    class_type field in the audit log to the class_desc field in
    sys.dm_audit_actions view.

Example:

Select top 10 action_id,name,class_desc,parent_class_desc from sys.dm_audit_actions

Result:

action_id, name, class_desc, parent_class_desc
----------------------------------------------
R   , REVOKE, DATABASE, SERVER
D   , DENY, DATABASE, SERVER
G   , GRANT, DATABASE, SERVER
GWG , GRANT WITH GRANT, DATABASE, SERVER
RWG , REVOKE WITH GRANT, DATABASE, SERVER
RWC , REVOKE WITH CASCADE, DATABASE, SERVER
DWC , DENY WITH CASCADE, DATABASE, SERVER
R   , REVOKE, OBJECT, DATABASE
D   , DENY, OBJECT, DATABASE
G   , GRANT, OBJECT, DATABASE

Example:

select top 20  * from sys.dm_audit_class_type_map

Result:

class_type, class_type_desc, securable_class_desc
------------------------------------------------
DB, DATABASE, DATABASE
OB, OBJECT, OBJECT
TY, TYPE, TYPE
SC, SCHEMA, SCHEMA
SX, XML SCHEMA COLLECTION, XML SCHEMA COLLECTION
AS, ASSEMBLY, ASSEMBLY
US, USER, USER
RL, ROLE, ROLE
AR, APPLICATION ROLE, APPLICATION ROLE
MT, MESSAGE TYPE, MESSAGE TYPE
CT, CONTRACT, CONTRACT
SV, SERVICE, SERVICE
BN, REMOTE SERVICE BINDING, REMOTE SERVICE BINDING
RT, ROUTE, ROUTE
FC, FULLTEXT CATALOG, FULLTEXT CATALOG
FL, FULLTEXT STOPLIST, FULLTEXT STOPLIST
SK, SYMMETRIC KEY, SYMMETRIC KEY
CR, CERTIFICATE, CERTIFICATE
AK, ASYMMETRIC KEY, ASYMMETRIC KEY
SR, SERVER, SERVER

Last
but not least is getting the actual data from the target files. This can be
retrieved using the following function.

SELECT session_server_principal_name, database_name,schema_name,Object_name, statement 
FROM sys.fn_get_audit_file ('C:\SQLAudit\MyDB_Database_*.sqlaudit',default,default);

Result:

session_server_principal_name, database_name, schema_name, Object_name, 
----------------------------------------------------------------------
statement
----------
WIN2K8-R2\Administrator, , , , 
MyLogin, MyDB, dbo, mytable, INSERT INTO [mytable] SELECT @1
MyLogin, MyDB, dbo, mytable, INSERT INTO [mytable] SELECT @1
MyLogin, MyDB, dbo, mytable, INSERT INTO [mytable] SELECT @1
MyLogin, MyDB, dbo, mytable, INSERT INTO [mytable] SELECT @1
MyLogin, MyDB, dbo, mytable, INSERT INTO [mytable] SELECT @1
MyLogin, MyDB, dbo, mytable, UPDATE [mytable] set [id] = @1  WHERE [id]=@2

Conclusion

Part 1 through part 4 of this series illustrated the
various components involved in SQL Server auditing as well as demonstrates how
to create server level, database level and audit level audit using action and
action group specification provided by Microsoft SQL Server 2008. This final part
of the series illustrated how to use SQL Server 2008 catalog view and dynamic management
view to access the audit details of existing server and database level audits.

Additional Resources

Catalog Views
(Transact-SQL)

Dynamic Management
Views and Functions

»


See All Articles by Columnist

MAK

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles