Grant View Server State

, 2014-04-02 (first published: )

HubbleThere 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

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads