SQL Server 2008 : Accessing Audit Details

March 3, 2010

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 

Click for larger image
Dynamic management view

Fig 1.3

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]

Click for larger image
verify the location and size

Fig 1.4

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

Click for larger image
Audit Properties

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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers