Find All Logins With VIEW SERVER STATE Permissions

Having survived my deep dive into Dynamic Management Views and Dynamic Management Functions in Microsoft SQL Server I thought it would be interesting to do a few posts here on some of the side issues that arise with use of these objects.  One thing to stress about usage of the DMOs is that it needs to be noted that a login must be explicitly granted VIEW SERVER STATE permissions before it can access the DMOs.  (Members of the sysadmin role already have this right implicitly granted.)

So this begs the question: How do I determine if a login has VIEW SERVER STATE permissions?  Well, you could open up each login’s properties window and check out their securables lists, or you could run the following script:

SELECT PRIN.[name] FROM sys.[server_permissions] PER 
sys.[server_principals] PRIN
ON PER.[grantee_principal_id] 
PRIN.[principal_id] WHERE PER.[permission_name] ‘VIEW SERVER STATE’
AND (PER.[state] ‘G’ OR PER.[state] ‘W’

The sys.server_permissions system catalog view contains a row for each server securable permission granted, revoked, denied, or granted with grant rights.  By limiting the [state] column to a value of ‘G’ or ‘W’ we only obtain hits where the login is granted this right. 

Granting this right to begin with is extemely straight-forward:

GRANT CONNECT SQL TO [some_login];