Select * from
(
select A.SQLloginame,Isnull(B.server_role,'Public') as ServerRole,A.createdate from (
select distinct name SQLloginame,* from sys.syslogins where hasaccess=1 and isntname=0 and name not in ('##MS_PolicyTsqlExecutionLogin##')
)as A
Left join (
SELECT @@SERVERNAME as SQLInstance, R.name AS server_role
, P.name AS role_member
FROM sys.server_role_members RM
INNER JOIN sys.server_principals P ON RM.member_principal_id = P.principal_id
INNER JOIN (SELECT principal_id, name FROM sys.server_principals WHERE type_desc = 'SERVER_ROLE') R
ON RM.role_principal_id = R.principal_id
) as B ON A.name=B.role_member
WHERE SQLloginame Not in ('##MS_AgentSigningCertificate##','##MS_PolicyEventProcessingLogin##')
--Order by SQLloginame
)as A
Left join
(
Select * from (
select [servername],[dbname],[UserName],[PermissionLevel] from [DB].[dbo].[Serverroles]
) src
pivot
(
Max(PermissionLevel)
for [dbname] in (DB1,DB2,DB3,DB4,DB5,DB6))piv
--order by UserName
)as B
On A.SQLloginame=B.[UserName]
Order by A.SQLloginame
