Posted Mar 3, 2010

SQL Server 2008 : Accessing Audit Details

By Muthusamy Anantha Kumar aka The MAK

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



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]



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



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



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



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



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')



Fig 1.7

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



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



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