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…
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.
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.