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 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:




No comments:
Write comments

Please do not enter spam links

Meet US

Services

More Services