Did you face the error - VIEW SERVER STATE permission was denied on object 'server', database 'master' while executing the dynamic management views or queries?
If yes, this article will help you eliminate the error.
About VIEW SERVER STATE Permission
The View Server State Permission allows users to execute and view results for server-scoped database management views\functions. For example, you get the error message if you run the DMV sys.dm_os_wait_stats to check to wait statistics in SQL Server without the View Server State permission.
SELECT wait_type, wait_time_ms
FROM sys.dm_os_wait_stats;
How to Grant VIEW SERVER STATE Permission
You can use a T-SQL statement or SQL Server Management Studio GUI to assign the permission.
T-SQL statement:
USE master GO GRANT VIEW SERVER STATE TO "LoginName"
GUI Steps:
Connect to the SQL Server Instance in SQL Server Management Studio.
Navigate to Security -> Logins and Right Click on your login name, and choose Properties
In the Securables tab, scroll for the option – View Server State and put a check on the Grant option as shown below.
Click Ok to apply for the View Server State permission and close the window.
To revoke the permission, you can uncheck the view server state permission from securable in SSMS or run the following T-SQL statement.
USE master
REVOKE VIEW SERVER STATE TO "LoginName"
GO
Comments