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 flexosql19.UserPermTest before correcting SqlInstance Database Name Login ----------- -------- ---- ----- FlexoSQL19 UserPermTest dbo FLEXOAndy FlexoSQL19 UserPermTest guest FlexoSQL19 UserPermTest INFORMATION_SCHEMA FlexoSQL19 UserPermTest Sql17User FlexoSQL19 UserPermTest sys
I successfully copied the database, but the user from the flexosql17 instance is still kicking around in that database. The flexosql19 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 flexosql19.UserPermTest before correcting SqlInstance DatabaseName User ----------- ------------ ---- FlexoSQL19 UserPermTest Sql17User
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 : FlexoSQL19 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.
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 flexosql19.UserPermTest after correcting SqlInstance Database Name Login ----------- -------- ---- ----- FlexoSQL19 UserPermTest dbo FLEXOAndy FlexoSQL19 UserPermTest guest FlexoSQL19 UserPermTest INFORMATION_SCHEMA FlexoSQL19 UserPermTest Sql19User Sql19User FlexoSQL19 UserPermTest Sql19User2 Sql19User2 FlexoSQL19 UserPermTest sys Orphan users in flexosql19.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-DbaDatabaseis a great choice, backup & restore, restore from a previous backup, whatever works for you) - Resolve orphaned users with
Remove-DbaDbOrphanUserand/orRepair-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.
The post Restoring Database Users After Copying the Database appeared first on FLX SQL with Andy Levy.