Database roles and permissions

Question

Good Morning, We have a database that has 50 database roles. I want to know which roles have VIEW SERVER STATE permission. How to get this information?

Answer 1

@Chaitanya Kiran
Please try below query. Hope it give you a results which you are looking for.

SELECT DP1.name AS DatabaseRoleName, DP2.name AS DatabaseUserName, DP2.type_desc AS UserType, DP2.default_schema_name AS SchemaName FROM sys.database_role_members AS DRM JOIN sys.database_principals AS DP1 ON DRM.role_principal_id = DP1.principal_id JOIN sys.database_principals AS DP2 ON DRM.member_principal_id = DP2.principal_id JOIN sys.server_permissions AS SP ON DP1.principal_id = SP.grantee_principal_id WHERE SP.permission_name = 'VIEW SERVER STATE'; This query joins the sys.database_role_members, sys.database_principals, and sys.server_permissions system views to get a list of all database roles that have the VIEW SERVER STATE permission. The result set includes the name of the database role, the name of the database user, the user type, and the default schema name. Thank you!

Answer 2

Answer: none of the database roles have VIEW SERVER STATE permission. To wit, database roles are database principals, and VIEW SERVER STATE is a server-level permissions that can only be granted to server principals.

This query returns the server principals that have been granted any of the three VIEW SERVER STATE permissions (SQL 2022 introduced two sub-permissions to VIEW SERVER STATE.):

SELECT l.name, p.permission_name
FROM   sys.server_principals l
JOIN   sys.server_permissions p ON l.principal_id = p.grantee_principal_id
WHERE  p.permission_name LIKE 'VIEW SERVER%STATE'

It does not include logins that have the permission through higher permissions like CONTROL SERVER or membership in sysadmin.