时间戳

首页 > 解决方案 > 使用只读用户访问 SQL Server 主数据库中的系统视图

sql-server - 使用只读用户访问 SQL Server 主数据库中的系统视图

问题描述

我在 SQL Server 中创建了一个用户，该用户只能以只读模式访问 2 个数据库，因此我为该用户分配了角色 db_datareader。

现在我需要这个用户能够访问主数据库中的系统视图，以便执行以下查询：

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

因此，我已使用 db_datareader 角色授予对主数据库的访问权限，因此用户只能对该数据库具有只读访问权限。

此外，我需要在 master 数据库中授予 sysadmin 服务器角色，以便此查询适用于该用户。我想知道这是否是正确的方法。或者还有其他更好的方法吗？授予 sysadmin 服务器角色听起来不太好......

更新：

我在下面创建了存储过程。如果我使用只读用户登录 Sql Server Management Studio 并按如下方式调用存储过程，则会收到一条消息错误：

exec [mySchema].[spGetNumberOfCurrentConnections] 


CREATE PROCEDURE [mySchema].[spGetNumberOfCurrentConnections] 
WITH EXECUTE AS 'dbo'
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    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
END

错误：

The EXECUTE permission was denied on the object 'spGetNumberOfCurrentConnections', database 'myDatabase', schema 'mySchema'.

更新2：

我已授予只读用户存储过程的 EXECUTE 权限。现在存储过程执行但它只返回 1 行这是不正确的，因为它应该返回至少 100 行。

如图所示，我已授予用户对存储过程的权限：

在此处输入图像描述

由于只读用户帐户没有“系统管理员”角色，它只显示一行。

如果我为只读用户帐户设置“系统管理员”角色，它会显示所有行（而不是像以前那样单行）。

标签： sql-serversql-server-2008sql-server-2012sql-server-2008-r2

解决方案

为只读用户设置 View SERVER STATE 权限有效。

问题是用户只看到当前会话，而不是 SQL Server 实例上所有正在执行的会话。

推荐阅读