Monday, June 27, 2011

Getting User Access of all Users in a Database

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