Identifying SQL Server logins with overlapping Server Roles

Here’s the query to find it…


SELECT SPMember.[login name], SPRole.name AS 'role name'

FROM master.sys.server_principals SPRole 
INNER JOIN master.sys.server_role_members SRM 
ON SPRole.principal_id = SRM.role_principal_id 
INNER JOIN ( SELECT SP.name AS 'login name', SP.principal_id 
FROM master.sys.server_principals sp_roles 
INNER JOIN master.sys.server_role_members SRM ON sp_roles.principal_id = SRM.role_principal_id
INNER JOIN master.sys.server_principals SP ON SRM.member_principal_id = SP.principal_id 
WHERE sp_roles.type_desc ='SERVER_ROLE'
GROUP BY SP.name, SP.principal_id 
HAVING COUNT(SP.name)> 1) AS SPMember 
ON SRM.member_principal_id = SPMember.principal_id 
ORDER BY SPMember.[login name] 

MvM