Dynamic Management Objects/ SQL/ Syndicated/ Tips and Tricks

Find All Logins With VIEW SERVER STATE Permissions

1 Comment

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 
  INNER JOIN 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’)
ORDER BY PRIN.[name]

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];