Here is a DMV script to check whether a or any database has the auditing configured and running, along with few other useful attributes if it does.
Few things to keep in mind:
- Auditing is available from SQL version 2008 and up....
- Until very recently (SQL version 2016 and up), granual (object level) auditing at the database level is available only on Enterprise edition.
- Although you could still use server level auditing to setup auditing at the database level
So keeping that in mind, I wrote this in a way to be able to execute as a multi-server query either using CMS or just a bunch of locally registered servers.
If you are running this as a multi-server script, you should turn on the "Merge Results" setting in the SSMS.
Finally, if you feel that this script is unncessarily complicated, its because it is written in a way so that it can be executed against any version and editon of sql server, whether it supports auditing or not!
Hope you find this use and please feel free to customize it anyway you would like...
SET nocount ON
USE [master]
go
IF Object_id('tempdb..##t1_db_audit_specs') IS NOT NULL
DROP TABLE ##t1_db_audit_specs
go
DECLARE @sql_version_number NVARCHAR(100)
DECLARE @edition NVARCHAR(100)
DECLARE @SQL NVARCHAR(2000)
create table ##t1_db_audit_specs
(
comment nvarchar(4000),
dbname nvarchar(256),
audit__target_name nvarchar(256),
db_audit_name nvarchar(256),
is_server_audit_enabled BIT,
is_db_audit_enabled BIT,
on_failure_desc NVARCHAR(256),
max_file_size BIGINT,
max_rollover_files INT,
queue_delay INT,
log_file_path NVARCHAR(520),
log_file_name NVARCHAR(520),
create_date datetime,
modify_date datetime,
sql_version_number NVARCHAR(256),
edition NVARCHAR(100)
)
SET @sql_version_number = Cast(Serverproperty('ProductVersion') AS NVARCHAR(100))
SET @edition = Cast(Serverproperty('Edition') AS NVARCHAR(100))
SET @SQL =
'
if ''?'' not in (''master'', ''model'', ''msdb'', ''tempdb'')
and CAST(DATABASEPROPERTYEX(''?'', ''status'') as varchar(50)) = ''ONLINE''
and CAST(DATABASEPROPERTYEX(''?'', ''Updateability'') as varchar(50)) IN (''READ_WRITE'', ''READ_ONLY'')
INSERT INTO ##t1_db_audit_specs
SELECT
null,
''?'' dbname,
sa.name audit_name,
das.name db_audit_name,
sa.is_state_enabled,
das.is_state_enabled,
sa.on_failure_desc,
sa.max_file_size,
sa.queue_delay,
sa.max_rollover_files,
sa.log_file_path,
sa.log_file_name,
das.create_date,
das.modify_date,
''' + @sql_version_number + ''' sql_version_number,
''' + @edition + ''' sql_edition
FROM [?].sys.database_audit_specifications das
inner join sys.server_file_audits sa on sa.audit_id = das.database_specification_id
'
print @sql
IF LEFT(@sql_version_number, Charindex('.', @sql_version_number) - 1) < 10 -- SQL version check
INSERT INTO ##t1_db_audit_specs (sql_version_number, edition, comment)
VALUES (@sql_version_number, @edition, '<<Auditing is not supported due to Older version>>')
ELSE
BEGIN
IF not exists (select * from sys.server_file_audits) -- Is Auditing supported?
INSERT INTO ##t1_db_audit_specs (sql_version_number, edition, comment)
VALUES (@sql_version_number, @edition, '<<There is no audit target is configured and therefore database audit could not have been created.>>')
ELSE IF (CAST(SERVERPROPERTY('Edition') AS VARCHAR(100)) NOT LIKE 'Enterprise%') -- Is database level auditing supported?
INSERT INTO ##t1_db_audit_specs (sql_version_number, edition, comment)
VALUES (@sql_version_number, @edition, '<<Granular auditing is not available in non-Enterprise editions of SQL Server.>>')
ELSE
EXEC master..sp_MSforeachdb @sql
END
if not exists (SELECT * FROM ##t1_db_audit_specs)
INSERT INTO ##t1_db_audit_specs (sql_version_number, edition, comment)
VALUES (@sql_version_number, @edition, '<<No database has auditing configured.')
SELECT * FROM ##t1_db_audit_specs
--
IF Object_id('tempdb..##t1_db_audit_specs') IS NOT NULL
DROP TABLE ##t1_db_audit_specs
go
