SQL Azure VIEW DATABASE STATE permission denied in database 'master'

Question

When I execute the following query:

SELECT * FROM sys.dm_io_virtual_file_stats(NULL, NULL)

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

SELECT * FROM sys.dm_io_virtual_file_stats(NULL, NULL)

from a user other than the provisioned (by SQL Azure portal) administrator user?

Solution 1

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.

Solution 2

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.

Comments

[Jack Richins]

What pricing tier is your database? Is it a basic, standard, or premium?

[datadev]

Is there a link that explains this somewhere?

[sports]

"On SQL Database Standard and Basic Tiers requires the SQL Database server admin account "... in my case the credentials are the same for the server and for the database... how can I connect only to the server so that I can execute the query?