How to save and then restore permissions after refreshing a database

Question

A database in a STAGING environment is overwritten every N weeks with a PRODUCTION copy that is more recent.

What is the proper way to save and then restore users, roles and explicitly granted permissions as they were before the existing database was overwritten by the production copy?

I do not want to restore users and permissions as they were in PRODUCTION, but as they were in the DEV environment before the database was overwritten.

This refresh process must be automated as there are many different environments with dozens of instances that can potentially host up to 10 databases.

Answer 1

@AaronBertrand is right. You should consider a different method of refreshing your staging environment. If for no other reason that any sensitive data that you have in production is now available to a new group of users who may not be as carefully restricted as in production. However I realize that changing your process can take time (if management will even approve the time).

Essentially you want to create a script that will drop all of your existing permissions from the copy of the prod database and another that will generate all of the permissions that used to be there. My personal preference is going to be to use a script like sp_dbpermissions. Run it on your production environment and then copy the drop user script column (second to last column) into a .sql file and save it. Be very careful not to run it in production of course.

EXEC sp_dbpermissions 'dbName'

You only need the second to last column from the first result set.

Next run the same script on your Staging database. This time you will want to copy the last column from each of the three result sets. This will give you the CREATE USER, sp_AddRoleMember and GRANT/DENY commands to add the permissions for this database. Again save them in a .sql file. After your restore go ahead and run the first script (to remove the old permissions) then the second (to add the new).

I also highly recommend taking a backup of your Staging database before overwriting it. This way if there is a problem you can always restore it back (even if to an alternate location) and confirm any missing permissions.

Answer 2

I have a similar requirement for environment refreshes where databases are too large for incremental data-diff-type refreshes and there is no line of sight from live to pre-prod anyway. I use Powershell for that these days. Basically from SSMS > Object Explorer > Start PowerShell from the server level:

## Script DROPs and CREATEs with sp_addrolemember OR ALTER ROLE
$server = Get-Item .
cd Databases\AdventureWorks2014\Users
$scripter = New-Object ("Microsoft.SqlServer.Management.SMO.Scripter") ($server)

$scripter.Options.ScriptDrops = $true

foreach ( $x in get-childItem ) { $scripter.Script($x) }

$scripter.Options.ScriptDrops = $false
$scripter.Options.IncludeDatabaseRoleMemberships = $true

foreach ( $x in get-childItem ) { $scripter.Script($x) }

This scripts out the relevant DROP/CREATE USER and accompanying sp_addrolemember or ALTER ROLE depending on the version of SQL Server. It's easy to adapt the script to output to file or run for multiple databases. The good thing about this method is that the Powershell lends itself to automation and is version agnostic. Sample output:

DROP USER [testUser]
CREATE USER [testUser] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]
ALTER ROLE [db_datareader] ADD MEMBER [testUser]
ALTER ROLE [db_datawriter] ADD MEMBER [testUser]