Thursday 25 June 2009

Get database roles users principles


   1:  SET NOCOUNT ON
   2:   
   3:  --Execute two select statements.
   4:  SELECT 'IF NOT EXISTS (SELECT NULL FROM sys.sysusers WHERE [name] = ' +
   5:  QUOTENAME(dp2.[name],'''') +  ') ' + CHAR(13) + '  CREATE USER ' +
   6:  QUOTENAME(dp2.name) + ' FOR LOGIN ' +
   7:  QUOTENAME(dp2.name)
   8:  FROM sys.database_principals dp
   9:  JOIN sys.database_role_members rm ON dp.principal_id = rm.role_principal_id
  10:  JOIN sys.database_principals dp2 on dp2.principal_id = rm.member_principal_id
  11:  WHERE dp2.[name] != 'dbo'
  12:  AND dp.[name] = 'GroupNameHere'
  13:   
  14:  SELECT 'CREATE ROLE [GroupNameHere] AUTHORIZATION [dbo]'
  15:  UNION
  16:  --Returns database role members.
  17:  SELECT
  18:  'EXEC sp_addrolemember N''' + dp.[name] + ''', N''' + dp2.[name] + ''''
  19:  FROM sys.database_principals dp
  20:  JOIN sys.database_role_members rm ON dp.principal_id = rm.role_principal_id
  21:  JOIN sys.database_principals dp2 on dp2.principal_id = rm.member_principal_id
  22:  JOIN sys.server_principals sp ON sp.sid = dp2.sid
  23:  WHERE dp2.[name] != 'dbo'
  24:  AND dp.[name] = 'GroupNameHere'

No comments: