There are lots of Dynamic Management Objects (DMO’s, more commonly referred to generically as DMV’s) that require extra permissions. Without having these permissions there are a number of diagnostic queries that you cannot perform. Whilst one way to deal with this is to make users a member of the sysadmin group, it really doesn’t adhere to Microsoft’s policy of least privileges. A permission required for a lot of these DMO’s is VIEW SERVER STATE.
As an example let’s create a test user called test1 with a password of test1. Yes, I know it’s a rubbish password, feel free to create your own:
USE [master] GO /****** Object: Login [test1] ******/CREATE LOGIN [test1] WITH PASSWORD=N'test1', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO
User created?
Good, now create a new query connected as test1 and run the following query. The query calls the dynamic management object sys.dm_os_wait_stats which is a fairly common dmv to call due to it’s popularity in diagnosing performance bottlenecks.
SELECT * FROM sys.dm_os_wait_stats
You’ll see an error like this one:
Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.
Nice and descriptive eh! So what permissions do we need? I’ll give you a clue, it’s mentioned in the title of this post. That’s right, well done, have a biscuit. The missing permission here is VIEW SERVER STATE.
Awesome, we now have a resolution. We just need a way of implementing it, thankfully this is a really easy task. Open up a new query window as an account with SysAdmin privileges and enter the following:
GRANT VIEW SERVER STATE TO test1
Assuming this worked successfully (if it didn’t then YOU don’t have permissions to assign it and shame on you for trying) go back to the query window that is being run under test1 and run the query against sys.dm_os_waits again. This time you will see a resultset.
In the same way that I granted privileges to the test1 login I could also do the same to a windows group or a server role. The following is just illustration purposes, please don’t do this in a production environment.
GRANT VIEW SERVER STATE TO [Public]
If I wanted to take away those privileges from the public server role then I could run one of the following, both would work.
REVOKE VIEW SERVER STATE TO [Public] DENY VIEW SERVER STATE TO [Public]
As a clean up task I would recommend that you drop the user test1 now
USE [master] GO /****** Object: Login [test1] ******/DROP LOGIN [test1] GO
I hope you found this post useful, if so please feel free to share it via one of the social media buttons below. If you are having other security problems, you may also want to check out this post on GRANT VIEW DEFINITION
