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)