SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

Mission: SQL Homeostasis

Drupal is a Senior SQL Server consultant with over 20 years of experience in engineering innovative SQL Server solutions for high growth organizations. A truly SQL Server specialist and IT generalist, Drupal is a certified MCDBA, Oracle OCP-DBA and IBM WebSphere Administrator. He is also ITIL V3 certified at the Foundation and Intermediate levels as well as PMP.

Comments

Leave a comment on the original post [sqlpal.blogspot.com, opens in a new window]

Loading comments...