Show Users and Roles - Shows Custom Groups too

Generic User and Rolename

SELECT UserName, RoleName
FROM
(
select b.name as USERName, c.name as RoleName
from dbo.sysmembers a join dbo.sysusers b on a.memberuid = b.uid
join dbo.sysusers c on a.groupuid = c.uid
)s
Group by USERName,Rolename
order by UserName,Rolename

Standard login users in roles

SELECT UserName, Max (CASE RoleName WHEN 'db_owner' THEN 'Yes' ELSE 'No' END) AS db_owner ,

Max(CASE RoleName WHEN 'db_accessadmin ' THEN 'Yes' ELSE 'No' END) AS db_accessadmin ,

Max(CASE RoleName WHEN 'db_securityadmin' THEN 'Yes' ELSE 'No' END) AS db_securityadmin ,

Max(CASE RoleName WHEN 'db_ddladmin' THEN 'Yes' ELSE 'No' END) AS db_ddladmin,

Max(CASE RoleName WHEN 'db_datareader' THEN 'Yes' ELSE 'No' END) AS db_datareader ,

Max (CASE RoleName WHEN 'db_datawriter' THEN 'Yes' ELSE 'No' END) AS db_datawriter ,

Max (CASE RoleName WHEN 'db_denydatareader' THEN 'Yes' ELSE 'No' END) AS db_denydatareader ,

Max (CASE RoleName WHEN 'db_denydatawriter' THEN 'Yes' ELSE 'No' END) AS db_denydatawriter

  FROM

  (

    select b.name as USERName, c.name as RoleName 

    from dbo.sysmembers a join dbo.sysusers b on a.memberuid = b.uid 

    join dbo.sysusers c on a.groupuid = c.uid

   )s

Group by USERName

order by UserName

Show SA's