Restoring Database Users After Copying the Database

A former colleague emailed me with a question about retaining/fixing database users and permissions after restoring a database. They were copying a database from one instance to another, with different logins, users, and permissions between the two instances. Backup & restore to copy the database is easy enough, but because users & permissions are kept inside the database itself, the destination environment loses all its permissions settings in the process. What to do?

The short answer? Script out all your user permissions before overwriting the destination database, or copy the users and their permissions to another database. Then run the script after restoring the database. Many years ago, we did this via T-SQL. This time around, we’ll do it with PowerShell.

The Scenario

Let’s say we have two environments, QA and Staging. Staging looks like production, aside from the fact that we don’t have real customer data in it. Periodically, we want to copy the Staging database down to QA for various reasons but the access controls in QA are quite different from Staging - there are logins and users in Staging that aren’t in QA (and vice versa). So each time we copy Staging down to QA, we have to reset the users in the database. Wouldn’t it be nice if we could make that easier?

Setup

I created two databases, one to use as my source and the other as the destination (which will be overwritten). Then I created logins on each instance and corresponding users in the databases. The users are named such that it’s really easy to see when the wrong user is in the destination database. As usual, I’m leveraging dbatools.

Let’s Go!

With setup out of the way, we’re ready to run.

Back it All Up

Start by backing everything up. Both the source and destination databases (just in case we need to revert quickly), as well as the users & permissions on the destination.

Backing up the databases is pretty straightforward and you’ve probably seen that before. The new bit is Export-DbaUser (note: As of this writing, this function is not compatible with PowerShell 7). This creates a file with all the user creation and GRANT statements needed to re-create the user(s) in the database. I used New-DbaScriptingOption to set some non-default options for the resulting script file, most notably the inclusion of an if not exists check so we don’t attempt to re-create a user that already exists. This prevents getting “user already exists” errors.

As you get more familiar with dbatools and the Export-* functions in particular, you’ll start finding more occasions to use ScriptingOptions to customize what’s happening and get something other than the SMO default behaviors. SMO offers quite a few options for generating scripts, and it’s worth taking a look so that you’re aware they exist and what’s possible. You don’t have to memorize them.

Restore the Database

Time to restore the database from our staging instance to QA. When I took the backups initially, I captured the output object so I’m able to use that to grab the name of the backup file created from staging and pass that in to Restore-DbaDatabase.

Restore-DbaDatabase -SqlInstance $DstInstance -Database $DBName -Path $Sql17Backup.FullName -WithReplace;

User Check

Immediately after restoring, check the list of users in the QA database. We’re looking for database users without a corresponding instance-level login, AKA orphaned users.

The output of Get-DbaDbUser:

Users in flexo\sql19.UserPermTest before correcting

I successfully copied the database, but the user from the flexo\sql17 instance is still kicking around in that database. The flexo\sql19 user is nowhere to be seen because they didn’t exist in the source instance. If I turn this over to the QA folks now, things will be broken.

User Sql17User is an orphaned user in the database now that it’s been restored to a new instance - they aren’t connected to an instance-level login. We can find all the orphaned users with Get-DbaDbOrphanUser to double-check this (the last 2 lines in the example above).

Orphan users in flexo\sql19.UserPermTest before correcting

User Cleanup

We don’t want orphaned users in our database, so we have two options - remove them or repair them. Repair-DbaDbOrphanUser will attempt to remap the user to a login with a matching name but different SID, while Remove-DbaDbOrphanUser` will remove any orphaned users outright. We’ll try a repair first.

Repair-DbaDbOrphanUser -SqlInstance $DstInstance -Database $DBName;

ComputerName : Flexo
InstanceName : SQL19
SqlInstance : Flexo\SQL19
DatabaseName : UserPermTest
User : Sql17User
Status : No matching login

No match found, but that’s expected. Let’s remove the orphaned users.

Remove-DbaDbOrphanUser -SqlInstance $DstInstance -Database $DBName;

Restoring Users

Now that the orphaned users are taken care of, what about that Sql19User who needs access to the database? We’ll execute the SQL script that was created by Export-DbaUser earlier to re-create them, then verify that all our database users are where they need to be.

And the results:

Users in flexo\sql19.UserPermTest after correcting

Orphan users in flexo\sql19.UserPermTest after correcting

Conclusion

This post looks like it’s a lot of code to deal with, but bear in mind that I also set up the scenario and demonstrated two ways to handle orphaned users with lots of review steps along the way. The key points to take away here are:

• Export the users in the target environment with Export-DbaUser
• Copy the database (Copy-DbaDatabase is a great choice, backup & restore, restore from a previous backup, whatever works for you)
• Resolve orphaned users with Remove-DbaDbOrphanUser and/or Repair-DbaDbOrphanUser
• Restore the original users in the target environment by executing the T-SQL script generated by Export-DbaUser.

Wrap this up in a function and schedule your environment refreshes or keep it in your back pocket for whenever you need it.