How can a SQL Server contained user get all active user connections to this contained database?(sys.dm_exec_sessions not getting all the results?)

Question

In the past, when using SQL Server login, this could be accomplished by below steps:

1. Run GRANT VIEW SERVER STATE TO /*server_login */ in the master database
2. Then run select * from sys.dm_exec_sessions as "server_login".

It would get all the connections, including all connections to my target database.

Now migrating to a "contained user" for a "contained database", I tried this:

1. Run GRANT VIEW DATABASE STATE TO /*contained_user */ in my target database
2. Then run select * from sys.dm_exec_sessions as "contained user"

It would only see myself, not other active connections to this target database.

The database in SQL Server 2019. What options do I have to allow a contained user to view all active connections to this database?

Answer 1

For SQL server, the permission VIEW SERVER STATE is still needed. Azure Database is different. I set up Azure Database today to verify that VIEW DATABASE STATE is sufficient.