How to save and then restore permissions after refreshing a database 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.
@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.