Friday, 2 September 2016

Query To Find SQL Audit Details

I've been deploying a SQL Server auditing solution for our new SQL 2014 estate. I know, we're a few years behind. But if you saw some of the legacy systems I work with you'd understand that migrating to SQL 2014 is quite a coup!

Anyway, I found a really great audit solution that I have used as a base on Colleen Morrow's blog. I will detail what I have added to her solution in a later post. But, for now, I would like to share a query that displays some basic information about existing audits, both at the server and database level.

I put this query together for the purposes of documentation. Our 2014 estate is getting large fast. And with so many installations, we need to keep track of what is installed and configured on all our servers.

The script below UNIONs two queries:
  • One for the Server audit specifications
  • One for the database audit specifications
The database audit specification is a dynamic SQL statement due to the fact that it needs to interrogate the system tables of individual databases for the audit details. I have put in explicit COLLATE hints to cater for servers where databases are not all in line with the server's collation.

Here's a sample result set: