Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’
Yesterday, one of the app user was complaining that he is not able to execute some of the queries and facing “VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ . (Microsoft SQL Server, Error 300) ” error while executing the script. The screenshot of this error is given below.
Solution – Error 300
We got this error because the login you are using to execute the script doesn’t have VIEW SERVER STATE permission. To fix this issue we will grant view server state permission to the login name.
We can assign this permission either using GUI in SQL Server Management Studio or we can simply execute a T-SQL command to get this done. I will explain both ways to assign this permission.
Using GUI in SQL Server Management Studio
Follow below steps to do it using GUI.
- Launch SQL Server Management Studio.
- Connect to the SQL Server Instance.
- Navigate to Security folder then expand Logins
- Right Click at your login name and choose Properties
- Click at Securables tab from left side pane.
- In the bottom pane, scroll to the bottom and click at Grant option for View Server State value.
- Now click on apply to close the window. You can now ask your user to test the script again. This time it will work.
Using T-SQL statement in SQL Server Management Studio
- Launch SQL Server Management Studio. Connect to the SQL Server Instance.
- Open New Query window
- Run below T-SQL statement
USE master GO GRANT VIEW SERVER STATE TO "LoginName"
Once you execute above command, you will have view server state permission on your login name.
- SQL Server AlwaysON Interview Questions & Answers
- SQL Server DBA Interview Questions & Answers
- Understanding Enhanced Database Failover in Always on Availability Group
- SQL Server Replication Interview Questions & Answers
Latest posts by Manvendra Deo Singh (see all)
- Understanding Hybrid Buffer Pool in SQL Server - December 30, 2018
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018
- SQL Server Error 5184: Cannot use file ‘I:\Path\Techyaz.mdf’ for clustered server - August 29, 2018