1

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)

Willy
  • 9,848
  • 22
  • 141
  • 284
  • My initial thought is to create a stored procedure that executes that SQL and then give your user execute permissions on it. Someone may come up with a better answer. – John Wu Aug 09 '19 at 00:11
  • @JohnWu Yes I have tried it but it does not work. I created a stored procedure containing that SQL query as well and then I gave execute permissions on it for this read only account but I get the same results, only one single row. Also I marked this stored procedure with execute as dbo but again this does not work. – Willy Aug 09 '19 at 00:49

2 Answers2

0

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rob
  • 628
  • 5
  • 9
  • I would like to not use execute as 'sa' because is so dangerous.... I have tried granting read permission (SELECT) on every database (both sys.databases and sys.sysprocesses) to the user but it is now working... – Willy Aug 09 '19 at 08:23
  • The purpose of the answers we give is to serve as guidance/examples, it is not meant to be the hard fast rule of how you must make your implementation. As sa is known to be a user with permissions far superior to the limited user account you are attempting to use, it is an easy way to show that we are creating a stored procedure a limited user account will be able to execute under the context of a different account. Nobody is forcing you to use sa in your own implementation. – Rob Aug 09 '19 at 21:17
0

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.

Willy
  • 9,848
  • 22
  • 141
  • 284