Hi,
Had a requirement to refresh the database in dev from prod but the criteria was to retain the user access for that db intact. So needed to work on this as a priority. I have broken down the task in a two phases.
1st phase:
To identify the script to generate the present user access for the users in a database. Below is mentioned the script which will help me get the list.
select su.name as UserName ,sg.name as AccessGroup from sysmembers sm
join sysusers su on sm.memberuid=su.uid
join sysusers sg on sg.uid=sm.groupuid
2nd phase:
To identify the script which will help me restore the access for the users in dev enviroment after the database refresh.
select 'Exec sp_addrolemember ' +''''+ sg.name+ ''''+','+''''+ su.name+''''
from sysmembers sm
join sysusers su on sm.memberuid=su.uid
join sysusers sg on sg.uid=sm.groupuid
Tushar
No comments:
Post a Comment