View database state permission limitations

Question

Hi All,

Please let me know the limitations of the "View Database State".

I have checked online and could see the VIEW SERVER STATE is server scoped. The user with this permission can view activity monitor and execute server-level DMVs and DMFs; if the user is granted VIEW DATABASE STATE Permissions, he can execute database-level DMVs and DMFs. Let me know if there are any additional limitations with the VIEW DATABASE STATE.

Thanks in Advance!

Answer 1

VIEW SERVER STATE is a server permission and is granted to a server principal, that is a login.

VIEW DATABASE STATE is a database permission and is granted to a database principal, that is a database user. And since it is a database permission it applies per database. VIEW DATABASE STATE in one database does not give you the same permission in another database automatically.

On the other hand, if you are granted VIEW SERVER STATE, you implicitly have VIEW DATABASE STATE in all databases you have access to. (But this could be counteracted with a DENY VIEW DATABASE STATE.

I will need admit that I have not explored in detail what you get with the respective permissions. Generally, for access to DMVs, that is sys.dm_xxxx, you need VIEW SERVER STATE, although in some cases you can see information for your own process without this permission. I am not aware of any DMV where you can see some information for only the database with only VIEW DATABASE STATE, but there very well be. A special case is Azure SQL Database, where the DMVs are per database, and you cannot have VIEW SERVER STATE.

An important thing you get access to with VIEW DATABASE STATE is the Query Store tables.