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'