At my office, we typically have three environments for each of our databases - Dev, Stage and Prod.  In most cases, permissions are very similar, but not exactly the same.  For example, service accounts and generic users usually have different names in each environment.  In some cases Windows groups will even have different permissions. For example, DEVDBreaders have access to dev servers, but not stage or prod.  For this reason, when we refresh an environment, i.e. restore Dev from a backup of stage, we must take into account these permissions differences and recreate them after the restore.

In response to this need, I developed a script which generates a backup script of all permissions in a given database.  It can be run on demand prior to a restore, or as I am doing, it can be scheduled to periodically script out permissions for all database on a server to eliminate the possibility of DBA forgetting to backup those permissions beforehand.

At the end of the day, you’ll be presented with a single script that uses print and select statements to generate a script to recreate all of your existing permissions.  I’ll walk you through how this works in detail, but from a high level, here we accomplish:

• Generate statements to recreate any user created roles in a database.
• Generate statement to grant users access to the database
• Generate statements to add users to roles
• Generate statements to recreate all object level permissions
• Generate statements to grant any database wide permissions
• Generate statement to drop any users which did not previously have access.  
• Finally, I’ll show you how I wrapped the entire thing in sp_msforeachdb and scheduled it through the sql agent to run against all my databases daily and output to a script file- kind of a daily “permissions backup”

For the sake of clarity, I should also point out what we do not do:

• It does not repair orphaned users - you still need to do that with sp_change_users login.  I’ve included a little reminder at the end of the script
• It does not revoke any object level permissions. If you granted Execute on the procedure sp_DropDatabase to public in production, then restore that database to Stage, running this script will not revoke that permission.
• The resulting permissions granting script does not run “error free”.  I choose these words carefully. There are some “normal errors” which are to be expected.   For example, if your developer created the procedure DropDatabase and granted permissions to it in Dev, those permissions will be scripted out.  If we restore from stage where DropDatabase was never created, we’ll still try to grant that permission and get an error like “the permission can’t be granted because the object doesn’t exist”.  This is not a problem since we assume our developers don’t care about the procedure if they asked us to refresh from another environment in which it does not exist.

Let’s have a look at the script.  This is heavily commented to help you understand what we are doing in each part:

/***SET RESULTS TO TEXT TO ENSURE FULL OUTPUT******//**
NOTE:  This script should be run once prior to a database restore to generate the
appropriate permissions scripts.  Set results to text or to file and save it.
First, you will run the script on the database prior to a restore and retain the output.
Second, restore the database, run the output of first script.
Then run the output of that script again to drop any users.
Finally repair any orphaned users.
Campbell  7-11-2011
**//*** NOTE- THIS SCRIPT RETAINS PERMISSIONS BUT DOES NOT REPAIR ORPHANED USERS
YOU MUST STILL RUN SP_CHANGE_USERS_LOGIN TO REPAIR ORPHANED USERS
***/-- The following line produces the USE statement
print 'USE ['+db_name()+']'
--next block enters some comments at the start of our script
SET NOCOUNT ON
print'--paste these results into new query window and run with results to text,'
print'--then execute the drop statement output again'
print'--permissions script for ' +db_name()+' on '+@@servername
print ' '
--If there are any user created roles, the following will recreate that role
if ((select COUNT (name) from sys.database_principals where type='R' and is_fixed_role =0 and name!='public') >0)
begin
print '--recreate any user created roles'
select 'create role ['+name+'] authorization [dbo]' from sys.database_principals where type='R' and is_fixed_role =0 and name!='public'
end
else
begin
print '--no user created roles to script'
end
print 'go'
--This next block creates the statements to grant users access to the database  This is
--our first opportunity for an "expected error".  If a user user exists in both environments,
--we try to grant it access and might get the error "the user already exists".  If so,
--we can just ignore that error.
print'--grant users access'
SELECT 'EXEC [sp_grantdbaccess] @loginame =['+[master].[dbo].[syslogins].[loginname]+'], @name_in_db =['+
 [sysusers].[name]+']'
 FROM [dbo].[sysusers]
 INNER JOIN [master].[dbo].[syslogins]
  ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid]
--WHERE [sysusers].[name]
print 'go'
--Now we add users to roles.  Pretty straight forward here:
PRINT '--add users to roles'
select 'EXEC sp_addrolemember ' + '@rolename=['+r.name+ '], @membername= ['+ m.name+']'
 from sys.database_role_members rm
  join sys.database_principals r on rm.role_principal_id = r.principal_id
  join sys.database_principals m on rm.member_principal_id = m.principal_id
 where m.name!='dbo'
 order by r.name, m.name
--Now we generate object level permissions. 
print 'go'
print '--object level perms'
select p.state_desc + ' ' + p.permission_name + ' ON [' + s.name +'].['+ o.name collate Latin1_general_CI_AS+ '] TO [' + u.name collate Latin1_general_CI_AS + ']' from sys.database_permissions p inner join sys.objects o on p.major_id = o.object_id inner join sys.schemas s on s.schema_id = o.schema_id inner join sys.database_principals u on p.grantee_principal_id = u.principal_id
print 'go'
--Following is database wide permissions.  for example, if you "grant execute to USER" and don't include an ON statement,
-- the object level permissions will not pick that up.  This does:
print '--grant database wide permissions'
select p.state_desc + ' ' + p.permission_name +' TO [' + u.name collate Latin1_general_CI_AS + ']' from sys.database_permissions p inner join sys.database_principals u on p.grantee_principal_id = u.principal_id
 where p.class_desc='DATABASE' and u.name !='dbo'
/**Next part generates a select statement which will create a "drop user" statement on the restored database. 
So, you will run the script, restore the database, run the output of first script,
then run the output of that script again... it's simpler than it sounds
**/print 'go'
PRINT'--list of users to drop'
select 'select ''drop user[''+name+'']'' from sysusers where name not in('
select ''''+name+''',' from sysusers
PRINT '''dropusername'')'
--In some cases, the script to "drop users" will want to drop a user that owns a schema and this will create an error.
--This is to be expected and you need to decide how you want to deal with it.  If there are no objects in the schema
--perhaps just drop it.  If the user owns objects, that needs to be dealt with differently and your cross environment
--restore needs to be discussed with the data owners. 
PRINT'--REMEMBER TO RUN SP_CHANGE_USERS_LOGIN'

Remember, this is a script that generates a script, so the results, when output to text or file look something like this:

USE [master]
--paste these results into new query window and run with results to text,
--then execute the drop statement output again
--permissions script for master on MYSERVERNAME
--recreate any user created roles
------------------------------------------------------------------------------------------------------------------------------------------------------------------
create role [RSExecRole] authorization [dbo]
go
--grant users access
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXEC [sp_grantdbaccess] @loginame =[sa], @name_in_db =[dbo]
EXEC [sp_grantdbaccess] @loginame =[SSRS], @name_in_db =[SSRS]
EXEC [sp_grantdbaccess] @loginame =[DOMAIN\user], @name_in_db =[DOMAIN\user]
EXEC [sp_grantdbaccess] @loginame =[##MS_PolicyEventProcessingLogin##], @name_in_db =[##MS_PolicyEventProcessingLogin##]
EXEC [sp_grantdbaccess] @loginame =[##MS_AgentSigningCertificate##], @name_in_db =[##MS_AgentSigningCertificate##]
go
--add users to roles
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXEC sp_addrolemember @rolename=[db_datareader], @membername= [SSRS]
EXEC sp_addrolemember @rolename=[RSExecRole], @membername= [SSRS]
go
--object level perms
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GRANT SELECT ON [dbo].[spt_fallback_db] TO [public]
GRANT SELECT ON [dbo].[spt_fallback_dev] TO [public]
GRANT SELECT ON [dbo].[spt_fallback_usg] TO [public]
GRANT EXECUTE ON [dbo].[usp_mystoredproc] TO [DOMAIN\user]
GRANT SELECT ON [dbo].[spt_monitor] TO [public]
GRANT SELECT ON [dbo].[spt_values] TO [public]
go
--grant database wide permissions
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GRANT CONNECT TO [dbo]
GRANT CONNECT TO 

GRANT CONNECT TO [##MS_PolicyEventProcessingLogin##]
GRANT CONNECT TO [##MS_AgentSigningCertificate##]
GRANT EXECUTE TO [##MS_AgentSigningCertificate##]
GRANT CONNECT TO [SSRS]
go
--list of users to drop
-------------------------------------------------------------
select 'drop user['+name+']' from sysusers where name not in(
-----------------------------------------------------------------------------------------------------------------------------------
'##MS_AgentSigningCertificate##',
'##MS_PolicyEventProcessingLogin##',
'db_accessadmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_ddladmin',
'db_denydatareader',
'db_denydatawriter',
'db_owner',
'db_securityadmin',
'dbo',
'DOMAIN\user',
'guest',
'INFORMATION_SCHEMA',
'public',
'RSExecRole',
'SSRS',
'sys',
'dropusername')
--RUN SP_CHANGE_USERS_LOGIN

So, we run the first script and create the output script above.  We then restore our database and run the script above to  grants all our permissions, and generate a third script as output from that.  This output will just be a few drop statements dropping any user which didn’t exist before the restore.  It might look like this:

drop user [domain\produser]
drop user [prodsqluser]

As far as the scripting goes, that’s the end of the line. You can paste the scripts above into SSMS, remembering to set output to text and all your permissions will be retained.  Or, if you’d like to take it one step further, you can wrap the entire script in sp_msforeachdb and output to text file on a scheduled basis.  I chose to do this from the SQL Agent.  Essentially, I just saved the script to a text file, in this case “d:\YOURPATH\retainperms.txt”  and called it with SQLCMD from xp_cmdshell.

Here’s how my job looks:

USE [msdb]
GO
/****** Object:  Job [permissionsbackup]    Script Date: 10/27/2011 09:53:14 ******/BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 10/27/2011 09:53:15 ******/IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
 EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'permissionsbackup',
            @enabled=1,
            @notify_level_eventlog=0,
            @notify_level_email=0,
            @notify_level_netsend=0,
            @notify_level_page=0,
            @delete_level=0,
            @description=N'No description available.',
            @category_name=N'[Uncategorized (Local)]',
            @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [run sql]    Script Date: 10/27/2011 09:53:15 ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'run sql',
            @step_id=1,
            @cmdexec_success_code=0,
            @on_success_action=1,
            @on_success_step_id=0,
            @on_fail_action=2,
            @on_fail_step_id=0,
            @retry_attempts=0,
            @retry_interval=0,
            @os_run_priority=0, @subsystem=N'TSQL',
            @command=N'sp_configure ''show advanced options'', 1
go
reconfigure
go
sp_configure ''xp_cmdshell'', 1
go
reconfigure
go
sp_msforeachdb''
xp_cmdshell ''''sqlcmd -S YOURSERVERNAME -d ? -i "d:\YOURPATH\retainperms.txt" -o "d:\YOUROUTPUTPATH\?_perms_YOURSERVERNAME.sql"''''''
go
sp_configure ''xp_cmdshell'',0
go
sp_configure ''show advanced options'', 0
go
reconfigure
go
',
            @database_name=N'master',
            @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'daily6pm',
            @enabled=1,
            @freq_type=4,
            @freq_interval=1,
            @freq_subday_type=1,
            @freq_subday_interval=0,
            @freq_relative_interval=0,
            @freq_recurrence_factor=0,
            @active_start_date=20111020,
            @active_end_date=99991231,
            @active_start_time=60000,
            @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

There is certainly some room for improvement.  One might reasonably question my choice to use xp_cmdshell.  In my environment, this is not really a risk worth worrying about.  This might not be the case for you.  Also, one could conceivably improve the code to avoid some of the “normal errors” I encounter and to revoke permissions that did not previously exist.   I’d be interested to hear other approaches to retention of permissions in cross environment restores.