Retrieve Auditing Configuration Details

Script to retrieve auditing configuration details in sql server. Auditing feature is released from SQL Server 2008 onward so if you have utilized this feature then you can make use of this script to get quick information about audit configuration.

Applies To

SQL Server 2008 & Above

Server Audit Details

SELECT a.name 'AuditName',a.type_desc 'AuditFileLocation',
CASE a.is_state_enabled WHEN 1 THEN 'Enabled' WHEN 0 THEN 'Disabled' END 'AuditStatus'
,b.name 'ServerAuditName', 
CASE b.is_state_enabled WHEN 1 THEN 'Enabled' WHEN 0 THEN 'Disabled' END 'ServerAuditStatus' 
,c.audit_action_id ,c.audit_action_name ,c.audited_result 
from sys.server_audits a
JOIN sys.server_audit_specifications b ON a.audit_guid = b.audit_guid 
JOIN sys.server_audit_specification_details c ON b.server_specification_id = c.server_specification_id

Database Audit Details

USE DBName 
GO
SELECT a.name 'AuditName',a.type_desc 'AuditFileLocation',
CASE a.is_state_enabled WHEN 1 THEN 'Enabled' WHEN 0 THEN 'Disabled' END 'AuditStatus'
,b.name 'DatabaseAuditName'
,CASE b.is_state_enabled WHEN 1 THEN 'Enabled' WHEN 0 THEN 'Disabled' END 'DatabaseAuditStatus'
,c.audit_action_id ,c.audit_action_name ,c.class_desc ,c.audited_result 
FROM sys.server_audits a
JOIN sys.database_audit_specifications b ON a.audit_guid = b.audit_guid 
JOIN sys.database_audit_specification_details c ON b.database_specification_id = c.database_specification_id