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.
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?
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.
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 -SqlInstance $DstInstance -Database $DBName -Path $Sql17Backup.FullName -WithReplace;
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
Users in flexo\sql19.UserPermTest before correcting SqlInstance Database Name Login ----------- -------- ---- ----- Flexo\SQL19 UserPermTest dbo FLEXO\Andy Flexo\SQL19 UserPermTest guest Flexo\SQL19 UserPermTest INFORMATION_SCHEMA Flexo\SQL19 UserPermTest Sql17User Flexo\SQL19 UserPermTest sys
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.
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 SqlInstance DatabaseName User ----------- ------------ ---- Flexo\SQL19 UserPermTest Sql17User
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
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;
Since there’s no output (and I didn’t use
-Verbose), we’ll assume that there was either nothing to be done (not true in this case, see above) or that it completed successfully. We’ll verify later.
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 SqlInstance Database Name Login ----------- -------- ---- ----- Flexo\SQL19 UserPermTest dbo FLEXO\Andy Flexo\SQL19 UserPermTest guest Flexo\SQL19 UserPermTest INFORMATION_SCHEMA Flexo\SQL19 UserPermTest Sql19User Sql19User Flexo\SQL19 UserPermTest Sql19User2 Sql19User2 Flexo\SQL19 UserPermTest sys Orphan users in flexo\sql19.UserPermTest after correcting
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
- Copy the database (
Copy-DbaDatabaseis a great choice, backup & restore, restore from a previous backup, whatever works for you)
- Resolve orphaned users with
- Restore the original users in the target environment by executing the T-SQL script generated by
Wrap this up in a function and schedule your environment refreshes or keep it in your back pocket for whenever you need it.