SQL Server 拒绝schema访问权限后如何授予该schema下部分表查询权限

首先明确SQL Server权限优先级规则：DENY > GRANT > 默认无权限，需先确认原有受限配置类型再选择对应方案：

场景1：仅未授予Schema访问权限（无显式DENY记录） #

这类场景用户默认没有整个Schema的访问权限，也没有被显式拒绝，直接授予指定表的查询权限即可：

-- 授予单个用户指定表查询权限
GRANT SELECT ON [受限Schema名称].[目标表名称] TO [目标用户名];

-- 批量授予同个用户多个表查询权限示例
GRANT SELECT ON [受限Schema名称].[表1] TO [目标用户名];
GRANT SELECT ON [受限Schema名称].[表2] TO [目标用户名];

-- 若需给一批用户授权，可将用户名替换为对应角色名，批量生效

场景2：已显式DENY了整个Schema的访问权限 #

由于DENY权限优先级最高，直接授予表权限会被上级Schema的DENY覆盖，可选择以下两种方案：

方案1：移除Schema级DENY后单独授权（推荐） #

移除全局Schema级DENY后，未显式授权的其余表用户仍无访问权限，符合最小权限原则：

-- 先移除对应用户/角色在目标Schema上的SELECT拒绝权限
REVOKE SELECT ON SCHEMA::[受限Schema名称] TO [目标用户名/角色名];

-- 再按需授予指定表的查询权限
GRANT SELECT ON [受限Schema名称].[目标表名称] TO [目标用户名/角色名];

方案2：通过所有权链绕过（不允许修改Schema级权限时使用） #

在用户有权限访问的Schema下创建指向目标表的视图，利用相同所有者的所有权链跳过基础表的权限检查：

-- 在用户可访问的Schema下创建对应表的视图
CREATE VIEW [可访问Schema名称].[v_自定义视图名]
AS
SELECT * FROM [受限Schema名称].[目标表名称];

-- 给用户授予视图的查询权限即可
GRANT SELECT ON [可访问Schema名称].[v_自定义视图名] TO [目标用户名/角色名];

权限验证方法 #

配置完成后可执行以下语句验证权限是否生效：

-- 切换为目标用户身份
EXECUTE AS USER = '[目标用户名]';

-- 测试查询目标表，正常返回数据则配置生效
SELECT TOP 10 * FROM [受限Schema名称].[目标表名称];

-- 切回原管理员身份
REVERT;

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