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

SELECT 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 <> 'public' ORDER BY Name, LoginType


The Output will look like below:

No comments:
Write comments

Please do not enter spam links

Meet US


More Services