Blog Post

Check if any database has auditing configured

,

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

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating