how to save logins and permission prior a db refresh
Posted on 2007-08-08
Hi Experts, I have a question about saving security login and permissions.
I have to refresh a test db with prd data. I understand I need to have the permissions of the logins so I can change them once the db is refreshed beacuse the permissions on the prd db are different than the permissions on the uat db. I have to save them prior the refresh and apply them after the refresh.
I got this script but when I run it on the uat db I get only 3 rows for dbo, a dba and a dbateam. I need to get logins of the developers and users of uat also but I don't get them. I don't understand why.
select 'exec sp_change_users_login ''update_one'',
''' + a.name + ''' , ''' + a.name + ''''
from sysusers a, sysxlogins b where a.issqlrole = 0
and a.sid = b.sid
exec sp_change_users_login 'update_one', 'dbo' , 'dbo'
exec sp_change_users_login 'update_one', 'DRDEKKER' , 'DRDEKKER'
exec sp_change_users_login 'update_one', 'dba_cur' , 'dba_cur'
Can someone please help me out here so that I can start the restore of the uat DB?