Automating another SQL Server auditing script.


In today’s blog post I’m leveraging the work of another SQL Server MVP, Jason Brimhall. This summer Jason contributed a blog post to the July session of TSQL Tuesday. TSQL Tuesday is “a monthly blog party on the second Tuesday of each month”


In Jason’s July blog,, he presented an elegant script for detecting audit events from the default trace log. This fit in perfectly with the auditing theme that’s preoccupied much of my time this summer. So, I set out to automate it in Powershell so that I could drive the automation from any server using my typical Powershell coding approach.

This turned out to be pretty easy…

stored procedure wrapper

A stored procedure and PowerShell wrapper

To do this I simply wrapped Jason’s code into a stored procedure, then invoked it for over a dozen instances with a PowerShell script. The code for the wrappers, both the stored procedure and the PowerShell script are here on Github.

This example is a little cleaner than a previous PowerShell script since I’m using a hashtable to hold the list of server instances.

The results of the script were really eye-opening, and in my shop, terrifying.

It’s more terrifying than my daughter’s cat … or blockchain secured nuclear codes … or guest users having admin rights.

However, now all the undocumented security changes going on in some of our database servers will be captured with the script.

Questions and a humble thanks.

If you have the same needs as me I hope this PowerShell wrapper helps. If you have any questions on the weird PowerShell leave an issue at this link, or a comment at the bottom of the page.

And thanks to Jason for sharing his wealth of knowledge in detail with the community.

Leave a Reply

Your email address will not be published. Required fields are marked *