Logins, Roles and Permissions , Sysadmins

find out what login has what role and who are sysadmins?


Step1:
SET NOCOUNT ON

CREATE TABLE #DatabaseRoleMemberShip
   (
        Username VARCHAR(100),
        Rolename VARCHAR(100),
        Databasename VARCHAR(100)
       
    )DECLARE @Cmd AS VARCHAR(MAX)DECLARE @PivotColumnHeaders VARCHAR(4000)           SET @Cmd = 'USE [?] ;insert into #DatabaseRoleMemberShip
select u.name,r.name,''?'' from sys.database_role_members RM inner join
sys.database_principals U on U.principal_id=RM.member_principal_id
inner join sys.database_principals R on R.principal_id=RM.role_principal_id
where u.type<>''R'''EXEC sp_MSforeachdb @command1=@cmd

SELECT  @PivotColumnHeaders =                        
  COALESCE(@PivotColumnHeaders + ',[' + CAST(rolename AS VARCHAR(MAX)) + ']','[' + CAST(rolename AS VARCHAR(MAX))+ ']'                    
  )                    
  FROM (SELECT DISTINCT rolename FROM #DatabaseRoleMemberShip )a ORDER BY rolename  ASC


SET @Cmd = 'select
databasename,username,'+@PivotColumnHeaders+'
from
(
select   * from #DatabaseRoleMemberShip) as p
pivot
(
count(rolename  )
for rolename in ('+@PivotColumnHeaders+') )as pvt'

--drop table #DatabaseRoleMemberShip
EXECUTE(@Cmd )      



Step2:
who are sys admins

SELECT   name,type_desc,is_disabled
FROM     master.sys.server_principals
WHERE    IS_SRVROLEMEMBER ('sysadmin',name) = 1
ORDER BY name