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.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$instance = 'MyServer'
$auditName = $instance+"_TestAudit"
$auditDir = '\\MyServer\D$\Audits\'
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $instance
$newAudit = new-object Microsoft.SqlServer.Management.Smo.Audit($srv, "$auditName")
$newAudit.DestinationType = [Microsoft.SqlServer.Management.Smo.AuditDestinationType]::File
$newAudit.FilePath = $auditDir
$newAudit.MaximumRolloverFiles = 10
$newAudit.MaximumFileSize = 100
$newAudit.QueueDelay = 1000
$newAudit.Create()
$newAudit.Enable()
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
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $instance
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.
$newAudit = new-object Microsoft.SqlServer.Management.Smo.Audit($srv, "$auditName") $newAudit.DestinationType = [Microsoft.SqlServer.Management.Smo.AuditDestinationType]::File $newAudit.FilePath = $auditDir $newAudit.MaximumRolloverFiles = 10 $newAudit.MaximumFileSize = 100 $newAudit.QueueDelay = 1000
And once we’ve got all of our properties set, we do the PowerShell equivalent of clicking OK: we create the audit and enable it.
$newAudit.Create() $newAudit.Enable()
Want to make sure our audit was created? We can list all of our audit objects like this:
$instance = 'MyServer'
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $instance
foreach ($a in $srv.Audits)
{
Write-Host $a.Name
}
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.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$instance = 'MyServer'
$database = 'AdventureWorks'
$auditName = $instance+"_TestAudit"
$specName = $instance+"_"+$database+"_AuditSpec"
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $instance
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $srv.Databases.Item($database)
## Set audit spec properties
$AuditSpec = new-object Microsoft.SqlServer.Management.Smo.DatabaseAuditSpecification($db, $specName)
$AuditSpec.AuditName = "$auditName"
## Set audit actions
$SpecDetail = new-object Microsoft.SqlServer.Management.Smo.AuditSpecificationDetail("SchemaObjectChangeGroup")
$AuditSpec.AddAuditSpecificationDetail($SpecDetail)
$SpecDetail = new-object Microsoft.SqlServer.Management.Smo.AuditSpecificationDetail("EXECUTE","OBJECT","dbo","usp_ChangeEmpSalary","public")
$AuditSpec.AddAuditSpecificationDetail($SpecDetail)
## Create and enable audit spec
$AuditSpec.Create()
$AuditSpec.Enable()
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.
## Set audit spec properties $AuditSpec = new-object Microsoft.SqlServer.Management.Smo.DatabaseAuditSpecification($db, $specName) $AuditSpec.AuditName = "$auditName"
Note that, if we wanted to create a server audit specification, we would use the ServerAuditSpecification class:
$AuditSpec = new-object Microsoft.SqlServer.Management.Smo.ServerAuditSpecification($srv, $specName)
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.
## Set audit actions
$SpecDetail = new-object Microsoft.SqlServer.Management.Smo.AuditSpecificationDetail("SchemaObjectChangeGroup")
$AuditSpec.AddAuditSpecificationDetail($SpecDetail)
$SpecDetail = new-object Microsoft.SqlServer.Management.Smo.AuditSpecificationDetail("EXECUTE","OBJECT","dbo","usp_ChangeEmpSalary","public")
$AuditSpec.AddAuditSpecificationDetail($SpecDetail)
And finally, we create the audit specification and enable it.
## Create and enable audit spec $AuditSpec.Create() $AuditSpec.Enable()
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.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$instance = 'MyServer'
$database = 'AdventureWorks'
$auditName = $instance+"_TestAudit"
$specName = $instance+"_"+$database+"_AuditSpec"
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $instance
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $srv.Databases.Item($database)
## Delete the audit spec
$auditSpec = $db.DatabaseAuditSpecifications | where {$_.Name -match $specName}
if ( $auditSpec.Name -eq $null )
{
Write-Host "Database Audit Spec $specName does not exist."
}
else
{
$auditSpec.Disable()
$auditSpec.Drop()
Write-Host "Database Audit Spec $specName dropped."
}
## Delete the audit object
$delAudit = $srv.Audits | where {$_.Name -match $auditName}
if ( $delAudit.Name -eq $null )
{
Write-Host "Audit $auditName does not exist."
}
else
{
$delAudit.Disable()
$delAudit.Drop()
Write-Host "Audit $auditName dropped."
}
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.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$instance = 'MyServer'
$masterDB = 'SQLAudit'
$masterSpec = "Master_DB_AuditSpec"
$targetDB = 'AdventureWorks'
$targetSpec = $instance+"_"+$database+"_AuditSpec"
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $instance
$mdb = New-Object Microsoft.SqlServer.Management.Smo.Database
$mdb = $srv.Databases.Item($masterDB)
$mAuditSpec = $mdb.DatabaseAuditSpecifications | where {$_.Name -match $masterSpec}
if ( $mauditSpec.Name -eq $null )
{
Write-Host "Master Database Audit Spec $masterSpec does not exist."
}
else
{
$tdb = New-Object Microsoft.SqlServer.Management.Smo.Database
$tdb = $srv.Databases.Item($targetDB)
$newAuditSpec = new-object Microsoft.SqlServer.Management.Smo.DatabaseAuditSpecification($tdb, $targetSpec)
$newAuditSpec.AuditName = $mauditSpec.AuditName
$newAuditSpec.AddAuditSpecificationDetail($mauditSpec.EnumAuditSpecificationDetails())
$newAuditSpec.Create()
$newAuditSpec.Enable()
Write-Host "Database Audit Spec $targetSpec created."
}
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!!
