I travel quite a bit and the month of October and November seem to be filled with half my life is going to be spent hopping from one airport to another. Not to mention the amount of screening that go through every single time in every flight. However strange as it might sound, in one of such airport transits I was waiting for my bag to be screened through the machine and I was watching the screen on the other side where the security officer sits. It was interesting because it was a very skillful task as he was finding a needle in a haystack (in the literal sense). He was able to figure out what items, sharp items and toys which are restricted were getting passed through. I was amused and was transported into a world of wonderland because it was quite a challenging task. With these memories not subsiding, I landed to my desired destination and started checking my mails. There was one mail that caught my attention because suddenly it was a junior DBA who was trying to give some permissions and was getting an error. I was quick to get interested because it was a topic around security and I wanted to crack it. The message stated like: Error Fix: Msg 300, VIEW SERVER STATE
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.
As I always mention, most of the SQL Server error messages these days are well documented and were self-explanatory. To make sure I got the error message right, I requested the person to send me the query that was being executed that caused the error. The reply was:
SELECT wait_type, wait_time_ms FROM sys.dm_os_wait_stats
This made complete sense and the solution for this was also simple. From a repro point of view, I created a user Pinal and tried to run the above command. And guess what, as luck might turn out – the same error message as described in the start. To mitigate this error, I was to give the following GRANT and the error message disappeared:
USE MASTER GO GRANT VIEW SERVER STATE TO Pinal
Just curious to know have you as a DBA ever granted this right to users? What was the scenario in your case? Would love to see why you don’t want to give to specific users? Will be a great learning for all of us. Let me know via the comments, please.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
8 Comments. Leave new
Hi Pinal,I am a developer we have requested our DBA to grant this permission.
We had several blocking issues in our Prod database.To execute(to view output of ) sp_who2 we requested DBA to grant this permission.
Though this is a high level permission, sometimes I do see DBA’s reluctant to grant this rights. The other way to mitigate is to create a SP with EXECUTE AS OWNER rights and encapsulate the query. This has worked for a number of them too. I think this is a great idea to write an addendum blog on this.
We have chosen not to GRANT VIEW SERVER STATE.
We had some concerns about:
– security – might there be data that the person could see that they don’t have access to otherwise?
– performance and resource consumption – some DMV queries can run for a long time, expecially if you don’t provide the right parameters – like figuring out fragmentation or missing indexes with the associated proc or query.
Also, our team (mainly one of my co-workers) wrote a utility that captures active session information every 15 seconds (configurable) and saves the history. We have created a proc that captures the same info and give developers rights to run the proc. Developers typically can answer maybe 70% of their questions with this and then if history is needed the involve. Very few developers ask for information that qould require GRANT VIEW SERVER STATE.
I am curious to know what others do and think. I look forward to others comments.
May God bless you and have a great day!
David
David — your concerns are legitimate regarding security. When enabled, GRANT VIEW SERVER STATE will allow viewing of SQL commands … so, any SQLs with personal information, such as SSNs and Credit Card information will be view-able.
not sure why an end users doing a query would need this information. Am I mis-understanding something? This is a new error that is occurring in SQL Server 2014
have not had this issue in SQL Server 2008 – 2012
i had the same problem after i copyed a productive database onto testserver.
sollution worked for me
thanks
I know that this is a late reply, but since I recently saw this same issue and google puts this page high up its list, I am going to put this here.
There is a known bug with Management Studio that manifests with this error every time a user tries to right click an object in a database that they have access to.
We recently upgraded a SQL installation from SQL 2012 to SQL 2016. Users who were still using SSMS 2012 suddenly got this issue. Upgrading SSMS to the latest version got rid of it.
You should definitely avoid blanket handing out restricted permissions like this. You may just be putting a Band-Aid on a symptom, not solving the original problem.
I require this permission to see the current command when executing a DACPAC