Find all server level permissions assigned to user defined SERVER roles

1

Please help me with a T-SQL for finding all server level permissions assigned to user defined SERVER roles such as VIEW ANY DATABASE, VIEW SERVER STATE, SHUTDOWN, ETC.

For example, in the screenshot attached, for the test role, it has VIEW ANY DATABASE.

what's the T-SQL to find the permissions for test server role.

Thanks

image001 (4)
image001 (4)672×760 14 KB

2

This should do it:

SELECT u.name user_name, p.class_desc permission_class, p.permission_name, p.state_desc permission_state
FROM sys.server_permissions p
INNER JOIN sys.server_principals u ON p.grantee_principal_id=u.principal_id
WHERE u.type_desc='SERVER_ROLE' AND u.is_fixed_role=0
1 Like
3

@robert_volk - very helpful! thanks so much! :smiley: