Sql server ошибка 300

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.

VIEW SERVER STATE Permission

This is high level server-level privilege which must not be granted to everybody. Only administrators must have privilege to use view server state permission but we can assign this permission to some users who want to see server level state of your SQL Server instance. Once you will grant this access to any login, he can see result of all DMVs.

Solution – ( Microsoft SQL Server, Error 300 )

We got SQL Server error 300 ( VIEW SERVER STATE permission was denied ) because the login you are using to execute the script doesn’t have this permission. To fix this issue we will grant view server state permission to the login name. This section will explain how to grant view server state permission to a login.

Grant VIEW SERVER STATE Permission

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.

1. Launch SQL Server Management Studio.
2. Connect to the SQL Server Instance.
3. Navigate to Security folder then expand Logins
4. Right Click at your login name and choose Properties
5. Click at Securables tab from left side pane.
6. In the bottom pane, scroll to the bottom and click at Grant option for View Server State value.
7. 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

1. Launch SQL Server Management Studio. Connect to the SQL Server Instance.
2. Open New Query window
3. 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. You can resolve SQL Server error 300 VIEW SERVER STATE permission was denied on object ‘server’, database ‘master issue by following above steps given in this article.

Revoke VIEW SERVER STATE Permission

If you have identified anyone who must not have this permission then you can go ahead and revoke his access post getting proper approvals. Make sure that identified login does not have any business justification before revoking his access. Once you have decided to go ahead to remove view server state permission then you should run below T-SQL statement to get this done. Below steps will tell you how to revoke view server state permission of any login.

USE master

REVOKE VIEW SERVER STATE TO “LoginName”

GO

View SERVER STATE permission has been revoked for identified login post successful execution of above statement.

I hope you like this article. Please follow our facebook page and Twitter handle to get latest updates.

Read More:

• 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