SQL Server Audit 302 Deploying Audit Solution with Powershell

Auditing doesn’t have to be scary. SQL Server Audit 302 – Deploying Audit Solution with Powershell is part of a blog series designed to help you audit database changes with SQL Server Audit. Contact us if you have any questions about how to select and implement the right auditing solution for your organization.

SQL Server Audit 302 – Deploying Audit Solution with Powershell

SQL Server Audit 302 – Deploying Audit Solution with PowershellOriginally published on ColleenMorrow.com.

This is it, ladies and gentlemen. One last post and I promise I won’t mention the word audit for at least a week.

Before we begin, let’s take a moment to recap what we’ve covered so far:

For the final installment of this series, we’re going to take everything we’ve learned and put it all together.


In case you haven’t been paying attention, I really don’t like pointing and clicking my way through a GUI to do, well, just about anything. For any task that needs to be done more that once, I’d much rather spend the time upfront automating it, in order to save time later. Besides, automating tasks is fun! So it only makes sense that I’d want to use the same approach to auditing.

In SQLAudit 201, we created a SQLAudit database to act as our central audit repository. That’s where we’re storing the info on our current audits, and importing data from our audit files into a table for permanent storage and reporting.

The other thing we’re going to use that SQLAudit database for is to hold our “master” database audit specifications. These “master” specs will be used as templates to deploy auditing to other databases/instances. How, you ask? By using PowerShell, of course!

How it Works

I’ve split the work into 2 scripts: one to deploy an audit, one to remove an audit. Both can be executed interactively or in batch mode.



  • Target Instance – where to deploy audit to
  • Target Database – database to deploy audit to; null for a server audit
  • Target Login – Login with permissions to manage audits
  • Target Password
  • Audit Specification – name of the master audit specification to be deployed
  • UpdateAudit – y/n flag to overwrite an existing audit object if found
  • UpdateSpec – y/n flag to overwrite existing audit specification if found

If executed interactively, the script will prompt the user for target instance and database, login and password. It will connect to master instance and list available audit specs in the SQLAudit database and any server audit specs starting with “master”. The user is then asked to specify an audit to deploy. The script verifies that the user specified a valid master audit specification and determines whether the selected spec is databsse or server audit specification.

The script then connects to target instance, and determines if the target audit object exists. If it doesn’t, the audit object is created and a record is inserted into the SQLAudit.dbo.AuditLocator table. If the audit does exist, the user is prompted to overwrite definition. This is useful if you’ve modified the location or file options in the master audit object and you want to propagate those changes. If the path to the audit file has been changed, the AuditLocator table will be updated accordingly.

If a database audit specification was selected, the script will connect to the target database and check for a pre-existing spec. If one is found, the user is asked if the apec should be overwritten. If yes, the specification will be dropped and recreated. (There didn’t seem to be any sense to writing a separate function to update an audit specification.) Otherwise, the new specification is created. The same general logic is used for a server audit specification.



  • Instance Name – name of the instance you want to stop auditing
  • Database Name – name of database being audited
  • Audit Specification Name – name of audit specification to remove.
  • Target Login
  • Target Password

If run interactively, the user is prompted for the target instance and database, login and password. The script connects to target instance and lists all existing server audit specs and any database audit specs in specified database. It then prompts the user for the audit spec to remove. The script verifies the selected spec name is valid, and, if so, deletes that specification. If that was the last audit specification associated with that audit object, the audit object is also dropped and the record in AuditLocator is updated to mark the audit as inactive.

Download the Scripts

I’ve packaged up my scripts into one handy-dandy zip file for your downloading pleasure.

SQLAuditScripts.zip contains:

  • SQLAuditDatabase.sql – Script to create the SQLAudit database and its objects.
  • Library-ManageAudits.ps1 – Library of PowerShell functions to drop, create, and update audit objects and audit specs.
  • DeployAudit.ps1 – Powershell script for deploying an audit.
  • RemoveAudit.ps1 – Powershell script for removing an existing audit.

Download - SQL Server Audit 302 Deploying the Audit Solution with Powershell
First Name*
Last Name*

Additional Tools You’ll Need

I’ve mentioned these before, in my Inventory scripts. If you haven’t already, you’ll need to download a couple of PowerShell functions.

Name: Write-DataTable.ps1

Author: Chad Miller

Loads data into from a datatable object into SQL Server tables

Name: Out-DataTable.ps1

Author: Chad Miller

Formats input into a datatable object which can then be imported into SQL Server using Write-DataTable.

Sample Deployment

Let’s say we want to audit DDL (a common theme for me). Start by creating a Master_DDLAudit audit object on whatever instance holds the SQLAudit database. Use a file output, specifying a UNC path to our central audit folder. This is where all our audits will write to, so make sure it’s accessible by all your potential target instances. We’re not actually auditing with this master audit, so leave it disabled.

Next create an audit specification, Master_Database_DDLAudit, in the SQLAudit database. Specify the SCHEMA_OBJECT_CHANGE_GROUP action, and assign the spec to the Master_DDLAudit audit. Again, leave the specification disabled.

Suppose we also want to create a security audit to monitor changes to logins and server-level permissions. These are instance-level events, so we need to create a server audit specification, rather than a database audit spec. I don’t want this output going to the same file as my DDL audit, so I’ll create a new audit object, Master_SecurityAudit. Then I’ll create a server audit spec called Master_Server_SecurityAudit.

Now if I want to deploy the DDL audit to my AdventureWorks2012 database, I simply run the DeployAudit script.


If I check my AuditLocator table, I see the audit has been added as an active audit.


To stop auditing DDL on AdventureWorks, I use the RemoveAudit script.


Since this was the only audit specification attached to this audit, a select on my AuditLocator shows that the audit is now marked as inactive. The audit object has also been removed from the instance.



That concludes this series on SQL Audit. I hope you’ve enjoyed it as much as I have and I really hope you’ve found it helpful for your own auditing needs. Please don’t hesitate to contact me with any questions or problems you run into with the scripts.

SQL Server Audit Series

This blog series was designed to help you audit database changes.  Contact us if you have any questions about how to select and implement the right auditing solution for your organization with SQL Server Audit.
  1. SQL Server Auditing – Getting Started
  2. SQL Server Audit 101 – Creating Basic Audit
  3. SQL Server Audit 102 – Reading Audit Output 
  4. SQL Server Audit 201 – Creating Audit Solution
  5. SQL Server Audit 301 – Using PowerShell to Manage Audits
  6. SQL Server Audit 302 – Deploying Audit Solution with PowerShell

About the Author

SQL Server Consultant

Colleen Morrow

UpSearch Alum Colleen Morrow is a database strategist, community advocate, author, blogger and public speaker. She is passionate about helping technology leaders use Microsoft's SQL Server to protect, optimize and unlock data's value.

Colleen has been working with relational databases for almost 20 years. Since 2000, Colleen has specialized in SQL Server and Oracle database management solutions. She excels at performance tuning, troubleshooting mission critical SQL Server environments, and training technology professionals and business users of all levels.

Since 2011, Colleen has maintained a SQL Server focused blog at http://colleenmorrow.com. She is an active member of the Ohio North SQL Server User Group, as well as a volunteer for the Professional Association for SQL Server (PASS). Colleen earned a Bachelor of Science in Computer and Information Systems from Cleveland State University.

Learn more about Colleen Morrow at https://upsearch.com/colleen-morrow/.

About UpSearch


UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

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