使用具有数据库角色db_datareader SQL Server 帐户获取与特定数据库的当前连接数

Question

我有一个帐户db_datareader在我的数据库test_db上设置了数据库角色。

使用SSMS，我使用此帐户(使用 SQL Server 身份验证(登录，并打开一个新的查询窗口，以便通过执行以下查询来获取与test_db数据库的当前连接数：

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

其中 database_id = 6 对应于test_db。

此查询返回一行而不是多行。

但是，从 SSMS 如果我使用另一个未设置db_datareader的帐户(使用 Windows 身份验证(登录，如果我从 SSMS 打开一个新的查询窗口并键入上面指示的相同查询，我将获得与test_db数据库的所有当前连接(多行(。

为什么？我需要获取与test_db数据库的所有当前连接，而不仅仅是当前用户登录的单个连接(具有db_datareader数据库角色集的只读帐户(

Answer 1

由于您希望返回信息的 SQL 用户没有适当的权限，因此您需要创建一个存储过程并使用 执行方式

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 

显然，您还需要向您使用的有限用户帐户授予对存储过程的执行权限

Answer 2

最后，我通过授予查看服务器状态权限来读取只读用户来解决，如此处所述。

SQL Server 未返回所有行的问题是只读用户看不到 SQL Server 实例上所有正在执行的会话。只读用户只能看到当前会话。因此，为只读用户设置查看服务器状态是有效的。