Sql-server – Adding Users after Database Restore

sql server

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

Best Answer

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'
Related Question