2

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?

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.

  • The DB is a Premium P2, so I've added VIEW DATABASE STATE in USER db as I've mentioned in the question. The account still can't access 'sys.dm_os_schedulers'. Does it require 'VIEW DATABASE STATE' in Master? if so, it's back to my original question, as this errors as 'Grantor does not have GRANT permission' when I try and GRANT with the Server admin account. – DBA-PJS Oct 27 '17 at 8:14
  • Updated my answer. Please let me know if you were able replicate what I did. – SqlWorldWide Oct 27 '17 at 14:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.