SQL Audit 301 – Using Powershell to Manage Audits

2012/08/13

Today we’re going to go over some very basic scripts to create, drop, and copy SQL Audit objects using Powershell and SMO. Managing SQL Audit objects via PowerShell is actually pretty simple, even for a newbie like me. And I’m proud to say that these might be the first PowerShell scripts I’ve written that were entirely my own and not based on someone else’s work. I might actually be learning something!… Nah, probably not.

Creating an Audit object

The first step in implementing SQL Audit is to create the audit object, so that’s where we’ll start. Let’s look at the whole script and then break it down.

The first thing we’re doing is simply declaring some variables to hold our instance name, the name of the audit we want to create, and the folder where we want our audit file to be written. For re-usability, we could even make these into parameters, but I wanted to keep this simple. Next we create a new SMO connection to our instance with the command

Once we’re connected to SQL Server, we can create a new audit class object and start assigning attribute values. Here, we’re setting the destination to a file, and the file path to our $auditDir variable. We set the maximum number of rollover files, the queue delay, etc. All of the available properties can be found here: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.audit.aspx, but everything I’m setting here should look very familiar if you’ve been following along in this series.

Creating an Audit Specification

Once we’ve got our audit object created, we need to create the audit specification. For this example, we’ll create a database audit specification, rather than a server audit specification, though there are only slight differences between the two. In this particular audit spec, we’ll audit DDL changes to our AdventureWorks database and we’ll audit the execution of a stored procedure call usp_ChangeEmpSalary. As before, let’s see the whole script and break it down.

Just like in the previous script we start out by defining our variables for instance, database, the audit we’re assigning this spec to, and the name of the new spec. Then we create our SMO objects for our server and database. Now we can get down to business. We start by creating a DatabaseAuditSpecification class object and setting its AuditName property to the name of the audit object we’re assigning this spec to.

Ok, so we’ve got our audit spec named and assigned, now we need to tell it what to audit. This is a two-step process in SMO, first we create an AuditSpecificationDetail object, and then we add it to the audit spec. I used these two actions to give you an idea of how to add an action group vs an individual action with more configuration options.

Dropping Audits and Audit Specifications

Dropping an audit or audit spec is even easier than creating one. You find the one that matches the name you’re looking for, you disable it and drop it. In fact, it’s so straightforward that I’m not going to step through this one. If you’ve been following along so far, you’ll see what I’m doing here.

Copying an Audit Specification

The last activity I want to cover today is copying an audit specification from one database to another. This way we can define a “master” audit spec to use as a template for deployment to other databases/instances. Let’s imagine I have a database called SQLAudit where I’ve created such a template. And I want to copy that audit spec definition to AdventureWorks.

The only somewhat tricky part of copying an audit spec definition is that you can’t directly copy the actions. You need to use the EnumAuditSpecificationDetails function to assign the details from the original specification to the new one. Everything else is easy.

What’s next?

Now that we’ve got the basics of managing audits using PowerShell under our belts, we’re going to use these skills to deploy the audit solution I talked about previously. Good stuff people!!