Get number of current connections to a specific database using an SQL Server account with db_datareader database role

Question

I have an account with db_datareader database role set on my database test_db.

Using SSMS I log in using this account (with SQL Server Authentication) and I open a new query window in order to get the number of current connections to test_db database by performing below query:

select * FROM sys.databases sd LEFT JOIN sys.sysprocesses sp ON sd.database_id = sp.dbid 
WHERE database_id = 6 AND LOGINAME IS NOT NULL

where database_id = 6 corresponds to test_db.

This query returns one single row instead of many rows.

However, from SSMS If I log in using another account (with Windows Authentication) that does not have db_datareader set, If I open a new query window from SSMS and type the same query above indicated I get all the current connections to test_db database (more than one row).

Why? I need to obtain all the current connections to test_db database, not only the single connection which current user is logged in (the read only account with db_datareader database role set)

Answer 1

Since the SQL user you wish to return the information won't have the appropriate permissions, you will need to create a stored procedure and use EXECUTE AS

CREATE PROCEDURE dbo.GetConnectedUsers  
WITH EXECUTE AS 'sa'  
AS  
    SELECT * 
    FROM sys.databases sd 
    LEFT JOIN sys.sysprocesses sp ON sd.database_id = sp.dbid 
    WHERE database_id = 6 AND LOGINAME IS NOT NULL 
GO 

Obviously you would also need to give the limited user account you are using execute permissions to the stored procedure also

Answer 2

Finally I have solved by granting VIEW SERVER STATE permission to read only user as explained here.

The problem that SQL Server were not returning all the rows is because the read only user was not seeing all executing sessions on the SQL Server instance. Read only user was only seeing the current session. So setting VIEW SERVER STATE for the read only user works.