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:




2 comments:

Unknown said...

Nice to know about sql audit information.

convert foxpro code to sql
convert visual foxpro database to sql
foxpro database to sql server
import dbf file into sql server

simbu said...

This is a nice post in an interesting line of content.Thanks for sharing this article, great way of bring this topic to discussion.
Selenium training in Chennai

Selenium training in Bangalore