Script to find server level roles assigned to Server level logins roles

Below script will give us all server roles assigned to all users except guest and public

Quick Download

/******************************************************/

SELECT a.name as Name,a.type_desc AS LoginType, a.default_database_name AS DefaultDBName,

ISNULL(SUSER_NAME(b.role_principal_id),'public') AS AssociatedServerRole, is_disabled

FROM sys.server_principals a

LEFT JOIN sys.server_role_members b ON a.principal_id=b.member_principal_id

WHERE a.is_fixed_role <> 1

AND a.name NOT LIKE '##%'

AND a.name <> 'public' ORDER BY Name, LoginType

/******************************************************/

The Output will look like below: