Month: February 2019

Retrieve Users who have VIEW SERVER STATE Permission

SQL

Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.

There are two types of dynamic management views and functions:

  • Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server.
  • Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database.

To view users who currently have VIEW SERVER STATE, execute the following query:

SELECT 
who.name AS [Principal Name],
who.type_desc AS [Principal Type],
who.is_disabled AS [Principal Is Disabled],
what.state_desc AS [Permission State],
what.permission_name AS [Permission Name]
FROM 
sys.server_permissions what 
INNER JOIN sys.server_principals who
ON who.principal_id = what.grantee_principal_id
WHERE
what.permission_name = 'View server state'
AND who.name NOT LIKE '##MS%##'
AND who.type_desc <> 'SERVER_ROLE'
ORDER BY
who.name

viewState

To grant permission:

GRANT VIEW SERVER STATE TO [UserId]