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?
Best Answer
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: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 runSELECT * FROM sys.dm_os_schedulers
in the context of user database. I was able to get result set.