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

Find Orphaned databases in SQL Server

Do you ever wonder if there are any databases in your environment that may just be there but not being used?

I needed to make a comprehensive list of such databases in order to clean up old databases and prep old/existing environment and migrate and consolidate them into a new sql server infrastructure.

So I wrote this DMV query to obtain when a database was last accessed for a user query as well as those that were left OFFLINE but never removed.

I executed this against CMS / Multi Server query an whoa within few seconds I had the complete list or rather a preliminary list for my intended purpose.

Couple things to note first:

1. I only tested and ran this against SQL versions 2008 and up.

2. It uses a global temp table and removes when done

Hope you find this use and please feel free to leave comments/feedback/suggestions for further improvement.

/*

By an orphan database, I mean a database that no one
is using anymore, essentially the users and apps

using this database either migrated to a different, 

possibly upgraded copy of this database or 

simply retired. 

Such databases can be a good 
candidate for taking them offline and
decommission.

*/


SET nocount ON
SET TRANSACTION isolation level READ uncommitted

USE master

go

IF Object_id('tempdb..##t_dba_db_last_access_stats') IS NOT NULL
  DROP TABLE ##t_dba_db_last_access_stats

go

DECLARE @db_activity_since INT
SET @db_activity_since = 90 -- days

IF Object_id('tempdb..##t_dba_db_last_access_stats') IS NULL
  CREATE TABLE ##t_dba_db_last_access_stats
    (
       db_name          NVARCHAR(256),
       db_status        NVARCHAR(256),
       last_user_seek   DATETIME,
       last_user_scan   DATETIME,
       last_user_lookup DATETIME,
       last_user_update DATETIME
    )

go

EXEC Sp_msforeachdb
  ' use [?] if db_id() > 4 begin insert into ##t_dba_db_last_access_stats SELECT   DB_NAME() db_name,   CAST(DATABASEPROPERTYEX(DB_NAME(), ''Updateability'') AS NVARCHAR(256)) db_status,   last_user_seek = MAX(last_user_seek),   last_user_scan = MAX(last_user_scan),   last_user_lookup = MAX(last_user_lookup),   last_user_update = MAX(last_user_update) FROM sys.dm_db_index_usage_stats AS i WHERE i.database_id = DB_ID() AND OBJECTPROPERTY(i.object_id, ''ismsshipped'') != 1 end '

SELECT Getdate()                [current_time],
       @@servername             sql_instance,
       (SELECT crdate FROM   sysdatabases WHERE  NAME = 'tempdb') sql_instance_up_since,
       db_name,
       db_status,
       Max(last_accessed_date)  last_accessed
FROM   ##t_dba_db_last_access_stats a
       UNPIVOT ( last_accessed_date
               FOR last_accessed_for IN (last_user_seek,
                                         last_user_scan,
                                         last_user_lookup,
                                         last_user_update ) ) AS last_accessed
GROUP  BY db_name, db_status
UNION ALL                       -- Lets also grab list of OFFLINE databases
SELECT Getdate()  [current_time],
       @@servername sql_instance,
       (SELECT crdate 
               FROM   sysdatabases 
               WHERE  NAME = 'tempdb') sql_instance_up_since,
       NAME  db_name,
       Cast(Databasepropertyex(NAME, 'status') AS VARCHAR(50)) db_status,
       NULL last_accessed

FROM   sysdatabases
WHERE  NAME NOT IN ( 'master', 'model', 'msdb', 'tempdb' )
       AND Cast(Databasepropertyex(NAME, 'status') AS VARCHAR(50)) = 'OFFLINE'
ORDER  BY db_name

IF Object_id('##t_dba_db_last_access_stats') IS NOT NULL
  DROP TABLE ##t_dba_db_last_access_stats

go

Download this script from GitHub:

SQL Server Orphan Databases