User and Role membership in SQL 2005

• MANU-J.

  SSC-Dedicated

  Points: 31126

  More actions

  July 16, 2008 at 10:39 am

  #129925

  Hi,

  I am using SQL 2005(sp2) and I am not able to locate the user/role membership script option as it was available in sql 2000. I can only script out users creation statements using the script wizard available in sql 2000.

  Can somone please help me with any script which can output the user creation and their role membership assignment statements in output.

  Thanks in advance,

  Manu

• Greg Charles

  SSC-Forever

  Points: 45403

  More actions

  July 16, 2008 at 12:42 pm

  #844007

  set nocount on

  select 'exec sp_grantdbaccess ' + 'create user ' + a.name + ' for login ' + b.name

  from

  dbo.sysusers a join master.sys.syslogins b on a.sid = b.sid where a.uid > 3

  and a.uid < 16384

  select 'exec sp_addrolemember ' + '''' + p.name + '''' + ',' + '''' + m.name + ''''

  from sys.database_role_members rm

  JOIN sys.database_principals p

  ON rm.role_principal_id = p.principal_id

  JOIN sys.database_principals m

  ON rm.member_principal_id = m.principal_id

  Greg