VIEW SERVER STATE

Quick reference post on the VIEW SERVER STATE permission within SQL Server. This is a server level permission that once granted enables a login to view the results of Dynamic Management Objects.

I find that it's typically used for troubleshooting or performance tuning related activities and is a good alternative to the good old sysadmin role membership route, especially for external people.

To demonstrate what the permission allows I'll first create a new login on a test instance with the following command:

CREATE LOGIN SQLClarity WITH PASSWORD = 'SQLCl@r1ty' 

Now I've logged into Management Studio with the credentials I've created above. So let's try to select records from a DMV, in this case my instances cumulative wait statistics:

SELECT * FROM sys.dm_os_wait_stats

I get the following error:

Msg 300, Level 14, State 1, Line 1
VIEW SERVER STATE permission was denied on object 'server', database 'master'.
Msg 297, Level 16, State 1, Line 1

The user does not have permission to perform this action.

SQL Server has been quite specific on how to resolve the issue by stating that the VIEW SERVER STATE permission was denied. 

There are a couple of ways we can grant this permission, from the server properties > permissions window as in the image below. Remember that although the error message indicates the issue is on the master database it is a server level permission not a database one (such as view database state). 

Or we can use T-SQL syntax such as the following:

GRANT VIEW SERVER STATE TO SQLClarity

Now the results from the DMV are visible without error. 

This is a really useful way of restricting access for what could typically be viewed as an administrative task, however, one final word of caution though. This permission is applied at the server level and gives access to all of the Dynamic Management Objects and whilst in this particular case something like wait statistics might not be that sensitive the DMVs and DMFs do expose a lot of information so you have to bear this in mind when applying this level of permission.