Just recently, One of our customers approached us with SQL error 300. He complained that he was 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 a script.
SQL Server error 300 happens When a login in SQL Server does not have VIEW SERVER STATE permission.
Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to resolve such SQL related issues.
How to fix SQL error 300 ?
Since the script doesn’t have VIEW SERVER STATE permission, we can fix this error by granting permissions to the login name.
We can assign the permissions using two ways.
Here are they:
1. Granting permissions using GUI in SQL Server Management Studio
i. First, we launch SQL Server Management Studio.
ii. Next, we connect to the SQL Server Instance.
iii. After that, we navigate to the Security folder and expand Logins.
iv. Then we right-click at our login name and choose Properties.
v. Here, we click on the Securables tab from the left side pane.
vi. In the bottom pane, we scroll down and click on the Grant option for View Server State value.
vii. Finally, we click on apply to close the window. We can now ask our user to test the script again. This time it will work.
2. Giving permissions using T-SQL statement in SQL Server Management Studio
i. First, we launch SQL Server Management Studio and connect to the SQL Server Instance.
ii. Next, we open the New Query window
iii. Then we run the below T-SQL statement:
GRANT VIEW SERVER STATE TO "LoginName"
Once we execute the above command, we will have to view server state permission on our login name.
[Need urgent assistance in fixing SQL errors? – We're available 24*7. ]