Why cannot I view the sessions from all users in azure sql database?

WisonHii 81

Currently, there is one user which has below permissions in one azure sql database:

SHOWPLAN

SELECT ON SCHEMA::SYS

VIEW DEFINITION

VIEW DATABASE STATE

VIEW DATABASE PERFORMANCE STATE

DB_DATAREADER

but I cann't find the session status from all users by sys.dm_exec_sessions, dm_exec_connections and dm_exec_requests.

From the MS site, https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-sessions-transact-sql?view=sql-server-ver16,

it says that

SQL Database: Requires VIEW DATABASE STATE to see all connections to the current database. VIEW DATABASE STATE can't be granted in the master database.

GeethaThatipatri-MSFT 29,387 Reputation points Microsoft Employee

2023-09-07T01:35:57.73+00:00
Hi,@WisonHii Thanks for posting your question in the Microsoft Q&A forum.

Let me check from my end and provide you with more details if this permission cannot be granted in the master database in Azure SQL Database., alternatively, you can try this query returns a list of all active sessions in the current database.

USE <user_database>; SELECT * FROM sys.dm_exec_sessions;

Regards

Geetha
WisonHii 81 Reputation points

2023-09-07T03:35:55.4533333+00:00
Hi @GeethaThatipatri-MSFT ,

I have checked in azure portal, there always be 2000+sessions under this database. but when querying below script, it only return less than 100 sessions

SELECT * FROM SYS.DM_EXEC_SESSIONS;
GeethaThatipatri-MSFT 29,387 Reputation points Microsoft Employee

2023-09-08T15:54:22.7966667+00:00

@WisonHii Do you have a user with VIEW DATABASE STATE permission in master?? That account must be member of MS_ServerStateReader, isn't it?

And what is the account? Is it an AAD based User?

Regards

Geetha
WisonHii 81 Reputation points

2023-09-12T08:07:05.02+00:00

Hi @GeethaThatipatri-MSFT ,

I use the AAD based user to access our SQL Database in Azure. This user is only available in the user database, not in master database.

Share via

Why cannot I view the sessions from all users in azure sql database?

Your answer