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)
