Automating another SQL Server auditing script.

Introduction.

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”

::fn_trace_gettable

In Jason’s July blog, http://jasonbrimhall.info/2018/07/10/just-cant-cut-that-cord/, 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.
https://github.com/kkarns/brimhall-automation

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.  
https://github.com/kkarns/brimhall-automation/issues
 
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 *