Sql-server – SQL DB After Restore Script execution

To change permissions within the restored database the account which the automation is using will need to be a member of the securityadmin fixed server role. That'll give you rights to create a user within the database and give that user permissions within the database.

Using sp_change_users_login requires that the login which is running it be a member of the db_owners fixed database role within the database you are running it.

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'