Note: Technical Talk being moved to:
www.nazimcricket.com/wiki


Wednesday, 14 April 2010

Recreate Users and Roles

At my workplace, there is often a need to capture user and role information prior to performing a database refresh, to allow existing user accounts and permissions to be re-created post-refresh.

I have come up with the following scripts to assist with this task:

List and generate scripts to recreate accounts with Domain Authenticaton:
select 'CREATE LOGIN [DOMAIN\' + name + '] FROM WINDOWS WITH DEFAULT_DATABASE=[ceinfraprd]' from dbo.sysusers where isntname=1  


List and generate scripts to reinstate the roles:
select distinct 'exec sp_addrolemember ' + '''' + y.name + ''', ' + '''' + x.name + ''''
  from
(select uid, name, groupuid
  from dbo.sysusers a
     , dbo.sysmembers b
 where a.uid = b.memberuid) as x,
(select uid, name, groupuid
  from dbo.sysusers a
     , dbo.sysmembers b
 where a.uid = b.groupuid) as y
where x.groupuid = y.uid

No comments: