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

audit_details_1

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

audit_details_2

Leave a Reply

Leave a comment

Your email address will not be published. Required fields are marked *

*

´╗┐
x

Related Posts

Backup Report
I was working on one the project and they were looking for a backup report which should specify whether it's physical or logical and few more det...
Identify Orphan Users in All the databases
There are lot of scripts available in the internet to find orphan users across the database. This will be my version fo script to find out orpha...
T-SQL Script to find the names of the StoredProcedure that has used dynamic SQL
This article has the script that will be useful to find the names of the  Stored procedure that has used dynamic sql within the definition of the...
powered by RelatedPosts