Grant View Server State - is it possible for a none SA user to have in Azure SQL?

Question

I'm trying to set up some scheduled jobs which will collect some server stats, using system DMV's in SQL Server Azure. If a none SA users uses 'sys.dm_os_schedulers', they get an error (below), query works fine for server admin.

VIEW SERVER STATE permission was denied on object 'server', database 'master'

The user is a member of DBManager Role in the MasterDB, and they are a member of the db_owner role in User DB, and have added VIEW DATABASE STATE in user db.

I've attempted to 'GRANT VIEW SERVER STATE to [user]' in Master DB, using the server admin account (set up via portal on initial database creation). However, this errors :-

Grantor does not have GRANT permission.

Is it possible to grant this? (which is what every post I've seen says), or how do I do it with the Server Admin account?

Answer 1

GRANT VIEW SERVER STATE is not supported in Azure SQL Database.

GRANT Server Permissions (Transact-SQL)

In order to use sys.dm_os_schedulers you need:

On SQL Database Premium Tiers, requires the VIEW DATABASE STATE permission in the database. On SQL Database Standard and Basic Tiers, requires the Server admin or an Azure Active Directory admin account.

Reference: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-schedulers-transact-sql

While testing this I also could not grant VIEW DATABASE STATE to a user in master database using SA account.

I grant VIEW DATABASE STATE to the user database using SA account. Then run SELECT * FROM sys.dm_os_schedulers in the context of user database. I was able to get result set.