Complete a Server Assessment – Database Audit Information

Below you will find the code to get information about database audits.  Simply select the text and copy to the Clipboard.

CREATE TABLE #AuditInfo
(AuditSpecName VARCHAR(200),
AuditName VARCHAR(200),
DatabaseName VARCHAR(200),
CreateDate DATETIME,
AuditActionName VARCHAR(200),
OnFailureAction VARCHAR(100),
AuditedResult VARCHAR(100))

DECLARE @cmd NVARCHAR(2000)
SET @cmd=‘USE [?]
INSERT INTO #AuditInfo
SELECT s.name
, a.name
, db_Name()
, s.create_date
, d.audit_action_name
, a.on_failure_desc
, d.audited_result

FROM sys.database_audit_specifications s
INNER JOIN sys.database_audit_specification_details d
ON s.database_specification_id = d.database_specification_id
INNER JOIN sys.server_audits a
ON a.audit_guid = s.audit_guid’

EXEC SP_MSFOREACHDB @cmd
SELECT * FROM #auditinfo
DROP TABLE #AuditInfo

This code was written using the Microsoft SQL Server documentation.  If you find that this code was copied from else where, please let me know so I can give proper credit.