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:




3 comments:

Vignesh PV said...

your information is really awesome as well as it is very excellent and i got more interesting information from your blog.
PHP Training in Chennai

Shalini said...

Superb. I really enjoyed very much with this article here. Really it is an amazing article I had ever read. I hope it will help a lot for all. Thank you so much for this amazing posts and please keep update like this excellent article.thank you for sharing such a great blog with us. expecting for your.
Digital Marketing Company in India
seo Company in India

Abiya Carol said...

Very nice post here and thanks for latest smartphone applications it .I always like and such a super colors of phone for these post.Excellent and very cool idea and great models and different kinds of the more information's.

Web designing training in chennai