Adding Users after Database Restore

Question

I have to restore our production database to a test instance on a daily bases. This test instance gives people access to the data that we do not want changing production data. When I restore the database there are logins in the test instance with access to the database that do not have a login or user account in production instance. So I have a script that goes and adds each user to the users list in the restored database on the test instance. This list of users changes more than I would like. So I am always making changes to the script.

Does any one know a way to pull a list of instance logins with access to the restored database and add them to the user list of the restored database. I have to add them with read, write and execute permissions

Thanks

• Aaron Bertrand: How about a user in the database tied to a login with the same name & sid on both instances but different passwords? You could even deny connect to the login in production.
• Dewitt Harrison: Good Idea. Had not thought of adding to production but deny access. But how do I add in both instances with the same sid. I have a few user that are in both but the sid's do not match and I have a script that fixes orphaned users I run after the restore.

Answer 1

If I follow what you're saying correctly. You have db users in test, which are not in prod. When you restore from prod, all these users lose access. At the moment you have a static script which puts all the access back after the restore. You need to do this more dynamically. What I would do is:

1. Before restoring the prod backup to test, run a script which dumps user access, look at the code example below.
2. Perform your restore & other scripts as per normal.
3. Run the script output created in step 1. to put the access back for test.

Try to structure user access around roles if you haven't done so already. This will simply the process.

Here's a script I use:

print'use ' + DB_NAME()
print 'go'
declare @output nvarchar(max)
set @output = '' 
select @output= @output + 
'CREATE USER ' + QUOTENAME(mp.name) + ' FOR LOGIN ' + QUOTENAME(sp.name)
+CHAR(13)
--, is_a_member_of_role_name=rpn.name
from sys.database_principals sp 
 inner join sys.database_principals mp on (mp.principal_id = sp.principal_id) AND mp.type <> 'R'
 left outer join sys.database_role_members rm on (rm.member_principal_id = mp.principal_id) 
 left outer join sys.database_principals rpn on (rm.role_principal_id = rpn.principal_id) AND rpn.type = 'R'
 inner join sys.database_principals rp on rm.role_principal_id = rp.principal_id
 where mp.name not IN ('sys','dbo','guest')
 group by mp.name, sp.name
print @output 
print 'go' 
set @output = '' 
select @output= @output + 
'EXEC sp_addrolemember ''' + rpn.name + ''',''' + mp.name + ''''
+CHAR(13)
--, is_a_member_of_role_name=rpn.name
from sys.database_principals sp 
 inner join sys.database_principals mp on (mp.principal_id = sp.principal_id) AND mp.type <> 'R'
 left outer join sys.database_role_members rm on (rm.member_principal_id = mp.principal_id) 
 left outer join sys.database_principals rpn on (rm.role_principal_id = rpn.principal_id) AND rpn.type = 'R'
 inner join sys.database_principals rp on rm.role_principal_id = rp.principal_id
 where mp.name not IN ('sys','dbo','guest')
 group by mp.name, sp.name, rpn.name
 print @output
print 'go'

• Dewitt Harrison: Thanks for the script. Exactly the problem I am having. Will set this up and give it a try.

Answer 2

Create a table in your test database with the users that should have access, create a restore script that copies that table into a temp table, do your restore, recreate the table in your test database, use that table to create the appropriate access permissions.