Storing and Applying SQL Server Database Permissions With PowerShell

This post relies on some source code!

To keep this post as short as possible and just hit some highlights, I’m using selected pieces of code from one of my github repositories. You can grab the code form this link. You can use it to follow along with the post, or just start using it if you don’t care about what I write. Either way is fine with me, but know what you’re running, and keep random people’s scripts from the internet out of production till you’re comfortable with them.

“Glory to Arstotzka!”

As a DBA, at least part of your job revolves around data security. Sure, you do a good job locking down production and making sure people can’t read, change, or otherwise access your databases unless explicitly allowed (I hope). Your non-production environments though, also probably have some specific access control to them too, right? For example, your QA environments may have elevated permissions for your testers, while your development and integration environments might be set so your developers can have free reign over almost everything in a database to quickly deploy and test new functionality before the code moves “up the stack” to new areas, with wholly different expectations on permissions.

Permission requirements for these environments can change over time, just like the code and data going into your databases. It’s hard to track permissions because a database permission is much more than just a user principal; database objects often contain permission definitions for GRANT and DENY states, and users may belong in certain database roles in one environment, but not another. This isn’t a big deal… until it is: sooner or later your data and code drift will be different than production, or maybe some new change really breaks an environment. Then, you’ll be asked to restore these environments to either an earlier version, or, more likely, you’ll be asked to “refresh” these editions to what is currently in production.

You probably already have a process for this, but how are you handling maintaining differences in permissions between environments? Wouldn’t it be nice if you had a way to quickly evaluate, store, and then re-apply permissions as part of refresh? Even better, wouldn’t it be cool if you could do this for all your databases on a given instance? Or what about all your instances in a given environment?

You can, and you can do it pretty easily with PowerShell.

“Our agents are ready.”

Traditionally, if you wanted to capture database securables and permissions, you’d query some dynamic management views and pull down the information. You could take that same code, write some loops in T-SQL, and pull out the code. Same for role members, or object permissions. The tricky part is doing it for all databases on a server, or doing it for more than one server at a time.

PowerShell looks to circumvent that a little, and it’s all done with a few object methods. Check this out:

Import-Module SQLSERVER
$Database = Get-ChildItem -Path SQLSERVER:\SQL\ServerName\InstanceName\Databases | Where-Object {$_.Name -eq "DatabaseName"}
$Database.Users.Script()

The above code does three things: first it imports the SQLSERVER PowerShell module.Then I am using Get-ChildItem  to open up the PSDrive created by the module to connect to a given SQL Server Instance (the hostname is “ServerName”, instance is “InstanceName” and could either “DEFAULT” or a named instance). Then, from the $Database  object I got from the second line, I am taking the .Users  property, and then calling the .Script()  method on Users. What I am left with is a series scripted-out statements for all the database users (for my local AdventureWorks copy):

Pretty neat, right? I love PowerShell for stuff like this; it takes away all the right-clicking in SSMS and puts that power into your, uh, shell. Huh. I think I finally understand where PowerShell gets its name.

Since we can script out the database users, every time we do a refresh or restore, we can run these scripts to recreate users that existed before the restore; you could just cycle through these various commands, run them, and then you’d be able to recreate your security settings automatically.

There’s more to it than this though: what about database role membership? Object permissions? Database-level permissions? How can we capture all those? For a quick look, let’s put all the database users into their own variable:

$users = $database.users

If we look at the structure of a user object with Get-Member, check out the methods, specifically, .EnumRoles()  and .EnumObjectPermissions() . If you call the .EnumRoles()  method on the a user object, it’ll list out all the database roles a given user is a member of. So that must mean if we run .EnumObjectPermissions() , we can get the user object’s specific object permissions, too, right?

Well, two out of three ain’t bad. So how do we that? Well, we need to introduce a little complexity. Instead of running that method on a user object, we need to run that on a database object, like this:

Look at that, a detailed report of everyone’s explicitly defined database object permissions.

“You must be detained for questioning.”

So now we know who the defined database users are, what roles they’re a member of, and that the detailed report of what each database object’s permissions structure looks like. The question now is, how can we turn this into an automated process to capture it all and tie it together.

To help facilitate the capture of permissions, we’re going to need some sort of structure to store the results of our scans. For that, we’ll use a datatable. Here’s what one would look like if we wanted to capture information about our users from above, including our creation script:

$userTable = New-Object System.Data.DataTable
$userTable.Columns.Add("CaptureID", [System.Guid]) | Out-Null
$userTable.Columns.Add("UserName") | Out-Null
$userTable.Columns.Add("CreateScript") | Out-Null

Why use a datatable here instead of, say, a pscustomobject? You’ll see in a little bit, but for now, just roll with it.

Now we need to develop a process to group the permissions together, by user. We can do that with multiple datatables, sort of like how you would create a series of database tables. Take a look at the following code snippet:

ForEach ($d in $databases) {
    $databaseName = $d.Name

    $captureGuid = New-Guid

    $row = $captureTable.NewRow()
    $row["CaptureID"] = $captureGuid
    $row["CaptureDateTime"] = $captureDateTime
    $row["ServerName"] = $servername
    $row["InstanceName"] = $instanceName
    $row["DatabaseName"] = $d.Name
    $captureTable.Rows.Add($row)

    $dbusers = $d.Users
    ForEach ($u in $dbusers) {
        $row = $userTable.NewRow()
        $row["CaptureID"] = $captureGuid
        $row["UserName"] = $u.Name
        $row["CreateScript"] = [string] $scripter.Script($u)
        $userTable.Rows.Add($row)
    }

    $dbRoles = $d.Roles
    ForEach ($r in $dbRoles){
        $roleMembers = $r.EnumMembers()
        ForEach ($m in $roleMembers) {
            $row = $rolesTable.NewRow()
            $row["CaptureID"] = $captureGuid
            $row["RoleName"] = $r
            $row["RoleMember"] = $m
            $rolesTable.Rows.Add($row)
        }
    }

    $dbPermissions = $d.EnumDatabasePermissions()
    ForEach ($p in $dbPermissions) {
            $row = $objectTable.NewRow()
            $row["CaptureID"] = $captureGuid
            $row["ObjectClass"] = "Database"
            $row["ObjectSchema"] = $null
            $row["ObjectName"] = $p.ObjectName
            $row["PermissionState"] = $p.PermissionState.ToString()
            $row["PermissionType"] = $p.PermissionType.ToString()
            $row["Grantee"] = $p.Grantee
            $objectTable.Rows.Add($row)
    }

    $objectPermissions = $d.EnumObjectPermissions()
    ForEach ($p in $objectPermissions) {
            $row = $objectTable.NewRow()
            $row["CaptureID"] = $captureGuid
            $row["ObjectClass"] = $p.ObjectClass.ToString()
            $row["ObjectSchema"] = $p.ObjectSchema
            $row["ObjectName"] = $p.ObjectName
            $row["PermissionState"] = $p.PermissionState.ToString()
            $row["PermissionType"] = $p.PermissionType.ToString()
            $row["Grantee"] = $p.Grantee
            $objectTable.Rows.Add($row)
    }
}

So here’s what we’re doing: for every user object we find in a given database, we’ll first script out their creation. Then, we’ll enumerate their role membership. Then, we’ll search through a collection of objects to find that user in the “grantee” property and capture the object’s information so we can re-apply it later. When we’re done, we’ll be left with three separate custom objects to hold our results. And for good measure, we’ll throw in a unique capture ID (a GUID) to help us tie all three objects together.

So great, now we have all that stuff, how do we store it outside of our current PowerShell session for use later? Well, I hate to alarm you, but you’re a DBA, and the “D” stands for “database” so you already have ways to store information, so why don’t we leverage that? We’ll take our datatable objects and write them to a database! We can create custom tables to hold the collection of objects we’re going to get from each database. Once that’s done, we’ll use a SqlBulkCopy object to map the object properties to database table columns, and then write the results to tables for querying later. Here’s a sample of how you could do that with the datatable that is holding the user information.

We’d then do that for each table we created:

$adminConnection = New-Object System.Data.SqlClient.SqlConnection
if ($repoInstanceName -ne "DEFAULT") { $repoServerName = "$repoServerName\$repoInstanceName" }
$adminConnectionString = "Server={0};Database={1};Trusted_Connection=True;Connection Timeout=15" -f $repoServerName,$repoDatabaseName

$bcp = New-Object System.Data.SqlClient.SqlBulkCopy($adminConnectionString)
$bcp.DestinationTableName = "Permissions.Captures"
$bcp.BatchSize = 1000
ForEach ($Column in $captureTable.Columns)
{
    [void]$bcp.ColumnMappings.Add($Column.ColumnName, $Column.ColumnName)    
}
$bcp.WriteToServer($captureTable)        

Breaking this down, here’s what we’re doing: we’re first creating a connection object with New-Object, then setting the connection from an honest to goodness connection string. Next, we create the SqlBulkCopy object and pass it the connection we created manually. Next, we set some properties for the SqlBulkCopy object. We tell it which table name we want to write to, and how big each batch should be for the batch.

The next loop inside of there matches the names of our columns in our datatable object to the columns in our target table. Since our datatable columns were created to match our table names, I just use the same value for both. Once all the columns in our datatable have been mapped to real table columns, we then call the .WriteToServer()  method on our SqlBulkCopy object and pass it our datatable object.

If that seems like a lot of work… you’re right. Why do this, then? Well, the short answer is that right now, for my time and effort, SqlBulkCopy is the easiest and most efficient way of doing inserts from PowerShell. There’s lots of people out there that have written community cmdlets that can help you, but if you find yourself in a situation where you need to store object information in a database, you might be better served using a datatable and using a SqlBulkCopy object to move it over.

“What about Write-SQLDataTable?”

A recent SQLSERVER module cmdlet addition, Write-SQLDataTable  seems like a great idea: a quick cmdlet that you can pipe object collections or data table objects to a database table. The reality is that it doesn’t work the way you think it would. For example, if you have a datatable object with 5 columns, and you want to write to a table that has more columns (or columns in a different order than your datatable) the query won’t work. So guess what happens if you want to write to a table with an identity column? Yup, it can’t handle it. That’s not to say I think this cmdlet is useless; in a pinch if you need to write an object to a database, you can use -Force to write the data to a new table and everything will work. Just don’t try to use it for existing tables unless everything matches perfectly.

Here’s the main script in action, scanning through the database and capturing each database’s permissions and storing them with a unique GUID:

“I have everything you ask for now.”

So now we have a database full of user objects, user role memberships, and permissions. Now what? Let’s write a quick script to do the reverse: we’ll query the database for the most recent capture information for a given server, then pull down the user creation scripts, the user role memberships, and then apply specific object permissions.

First we’ll use some query methods on database objects to pull down datasets to match the server we want to apply permissions to:

$RefreshID = ($Repository.ExecuteWithResults("SELECT TOP 1 CaptureID, CaptureDateTime, ServerName, InstanceName, DatabaseName FROM Permissions.Captures WHERE ServerName = '$servername' AND InstanceName = '$instanceName' AND DatabaseName = '$CurrentDatabase' ORDER BY CaptureDateTime DESC")).Tables[0].CaptureID
if ($RefreshID) {
    $Results.PermissionSet = $RefreshID
    $Users = ($Repository.ExecuteWithResults("SELECT CreateScript FROM Permissions.DatabaseUsers WHERE CaptureID = '$RefreshID' ORDER BY ID")).Tables[0].CreateScript

Those four lines are from the top of the loop in the “set” function. First we send a query to the repository for a given database. If we get a result (the if test below that line), then we set the $Users object to contain the datatables from the database that return the set permissions for that given database. From there we’ll loop over other datasets related to users, role members, and then finally object permissions. It starts out looking easy, but you can see it quickly gets a little hairy; that’s because different types of permissions require different statements to work; for example, granting SELECT on a schema is just the schema name, and granting SELECT to a table is a two-part object name. There’s other scenarios you need to account for, too. Thankfully, we have the handy switch statement in PowerShell to make this a little more readable.

The output of the following switch statement will then be used in our query to apply the securable:

switch ($dbop.ObjectClass) {
    Schema {$statement = $dbop.permissionState + " " + $dbop.PermissionType + " ON SCHEMA :: [" + $dbop.ObjectName + "] TO [" + $dbop.Grantee + "]"}
    default {$statement = $dbop.permissionState + " " + $dbop.PermissionType + " ON [" + $dbop.ObjectSchema + "].[" + $dbop.ObjectName + "] TO [" + $dbop.Grantee + "]"}
}

And we just keep going. The set script gets complicated because of the differences in some SQL Servers for setting role members, and how each permission statement gets crafted. You can scroll down through the sample code to see how I’m accomplishing it.

The real kicker here is when the function finishes, we’ll get an object returned of each database that contains every successful application of a permissions or securable, and ones that fail. That means you can quickly view what had problems and what didn’t:

In the example above, I’m running set and putting the return object into $results . I then get all the results just for AdventureWorks2014 to see how many statements were successfully run, and if any failed to apply. Looks like I had just one, so I can filter that down some more with Select-Object  to just see the error statements (and I only had one, since you can’t do that with dbo).

I like using a script like this for my restores because as permissions change in a database, I don’t need to concern myself with capturing any changes manually; if I run the “get” function ahead of a refresh and then run the “set” function after, every permission should come right back to where it was.

Want to try it yourself? I put the code over on my github. Inside the repository you’ll also find a couple .SQL files that contain all the tables I’m using to store the permissions, as well as a helper stored procedures should you want to quickly query the data yourself. Oh, and in case it’s not abundantly clear: these PowerShell functions also require the SQL Server PowerShell module, which you can grab from here.

Take a look and let me know what you think, and if you feel this might help your non-production restores be a little more automated!

PS: Also, go play “Papers, Please.” Fantastic game where the section titles and animated .gif are from.