Solved issue: The EXECUTE permission was denied on the object xpsqlagentnotify, database mssqlsystemresource, schema sys. Explained what is RSExecRole in SQL Server Reporting Server. Msg 229, Level 14, State 5, Server InstanceName, Procedure spnotifyoperator, Line 1 The EXECUTE permission was denied on the object 'spnotifyoperator', database 'msdb', schema 'dbo'. By reading above error, it is pretty clear that particular user doesn’t have access to execute spnotifyoperator stored procedure in msdb database.
When I execute the following query:
I get the error:
Msg 262, Level 14, State 1, Line 4 VIEW DATABASE STATE permission denied in database 'master'. Msg 297, Level 16, State 1, Line 4 The user does not have permission to perform this action.
But I able to execute this query using the provisioned (by SQL Azure portal) administrator user.
I am unable to GRANT VIEW DATABASE STATE for master (using the provisioned administrator user) to any of the user I created, I get the following error:Grantor does not have GRANT permission.
Any ideas how to be able to execute the query
from a user other than the provisioned (by SQL Azure portal) administrator user?
Chirag Thakar2,31355 gold badges2727 silver badges4242 bronze badges
datadevdatadev
2 Answers
On SQL Database Premium Tiers requires the VIEW DATABASE STATE permission in the database. Permissions can not be granted in Master, but the views can be queried in user databases. On SQL Database Standard and Basic Tiers requires the SQL Database server admin account due to security requirements following from multi tenancy of those tiers.
Jack RichinsJack Richins
Jack is right.
On multi tenant database tiers, only server admin can query the views those are scoped to the server (requires VIEW SERVER STATE permissions) and those scoped to database can be queried by whoever has permissions to view_database_state permissions.
For Premium databases, DBO can query DMVs those are scoped to Server and database.
On master database of a server, no one has view database / view server permissions.
Satya_MSFTSatya_MSFT
Not the answer you're looking for? Browse other questions tagged sql-serverazureazure-sql-database or ask your own question.
In SQL Server 2008 there is a permissions VIEW SERVER STATE. What rights this permission give to user? What SQL Server mean by SERVER STATE?
hytyhyty
1 Answer
Read Dynamic Management Views and Functions
Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.
There are two types of dynamic management views and functions:
- Server-scoped dynamic management views and functions. These require
VIEW SERVER STATE
permission on the server. - Database-scoped dynamic management views and functions. These require
VIEW DATABASE STATE
permission on the database.
4,67722 gold badges2020 silver badges2323 bronze badges