2

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.

  • Maybe you should look into a different way of refreshing staging than blindly overwriting it - many tools out there will allow you to migrate schema/data changes without affecting user permissions. Might be faster, too, depending on how much has actually changed. – Aaron Bertrand Nov 24 '14 at 15:13
  • 1
    Add all necessary DEV permissions to a script (or more scripts). Run this script(s) after refresh. Keep the script in source control. – Remus Rusanu Nov 24 '14 at 15:14
  • Hello @RemusRusanu, that is definitely doable, but to start, I need to be able to at least store what's currently there. – Craig Efrein Nov 24 '14 at 15:22
5

@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'

enter image description here

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.

  • 1
    In case you are interested it was originally intended to do research not scripting. You may find you have to delete a few lines of code that reference fixed principals (dbo for example). I think I got rid of all that but might have missed something. If you run into any problems let me know. – Kenneth Fisher Nov 24 '14 at 17:22
  • hello Kenneth, the script does what I need, thank you again for your answer. – Craig Efrein Nov 25 '14 at 8:38
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]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.