如何实现让普通用户查看特定数据库角色成员的存储过程？

普通用户直接执行你提供的查询只能看到自己，是因为sys.database_principals系统视图的权限限制——默认情况下，普通用户仅能查看自身的主体信息。以下是两种可行的解决方法：

方法一：创建带权限提升的存储过程（推荐） #

通过给存储过程设置EXECUTE AS OWNER，让存储过程以所有者的权限执行（所有者需具备查看系统视图的权限，比如db_owner角色成员），同时仅给普通用户授予存储过程的执行权限，既满足需求又保证安全性。

创建存储过程的代码：

CREATE PROCEDURE GetHSRoleMembers
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;
    SELECT 
        r.name AS role_name, 
        m.name AS member_name 
    FROM sys.database_role_members rm
    INNER JOIN sys.database_principals r 
        ON rm.role_principal_id = r.principal_id
    INNER JOIN sys.database_principals m 
        ON rm.member_principal_id = m.principal_id
    WHERE r.name = 'HSS';
END;
GO

给普通用户授予执行权限：

GRANT EXECUTE ON GetHSRoleMembers TO [你的普通用户账号];
GO

之后普通用户只需执行EXEC GetHSRoleMembers;就能看到'HSS'角色的所有成员。

方法二：直接授予用户系统视图权限（不推荐） #

如果不需要严格的权限控制，可以直接给用户授予查看系统视图的权限，但这样用户能访问更多数据库主体信息，存在权限溢出风险：

-- 授予查看数据库所有主体定义的权限
GRANT VIEW DEFINITION ON DATABASE::[你的数据库名称] TO [普通用户账号];
-- 或者仅授予sys.database_principals的查询权限
GRANT SELECT ON sys.database_principals TO [普通用户账号];

授予权限后，普通用户就能直接执行你原有的查询语句查看所有成员。

内容的提问来源于stack exchange，提问作者user763539