Azure SQL数据库：阻止AD用户访问特定表的权限配置失效求助

问题核心 #

你将AAD用户peter@xxxxxx.co.uk加入了db_datareader角色（该角色默认拥有所有表的SELECT权限），之后执行DENY权限操作但未生效，用户仍能查询目标表。

排查与解决方案 #

1. 确认用户的角色权限继承 #

首先检查该用户是否拥有其他高权限角色（如db_owner、db_datawriter），这些角色的权限会覆盖DENY设定：

-- 查询用户所属的所有数据库角色
SELECT r.name AS 角色名称, m.name AS 用户名
FROM sys.database_role_members drm
JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
JOIN sys.database_principals m ON drm.member_principal_id = m.principal_id
WHERE m.name = 'peter@xxxxxx.co.uk';

如果发现用户除了db_datareader还有其他高权限角色，先移除这些角色：

ALTER ROLE db_owner DROP MEMBER [peter@xxxxxx.co.uk];
ALTER ROLE db_datawriter DROP MEMBER [peter@xxxxxx.co.uk];

2. 验证用户实际拥有的权限 #

执行以下SQL模拟用户权限，查看目标表的实际权限情况：

-- 切换到目标用户上下文
EXECUTE AS USER = 'peter@xxxxxx.co.uk';
-- 查询用户对目标表的权限
SELECT permission_name, state_desc
FROM fn_my_permissions('dbo.UpdatedProducts', 'OBJECT');
-- 切回原用户
REVERT;

如果结果中仍显示SELECT权限为GRANT，说明权限继承未被正确阻断。

3. 正确的权限配置方案 #

方案A：移除db_datareader，采用自定义角色精细授权

db_datareader是批量授权所有表的SELECT，建议替换为自定义角色来实现“允许大部分表，拒绝特定表”的需求：

-- 1. 创建自定义只读角色
CREATE ROLE CustomDBReader;
-- 2. 授予角色所有表的SELECT权限（除了要拒绝的表）
GRANT SELECT TO CustomDBReader;
-- 3. 拒绝角色访问目标表
DENY SELECT ON dbo.UpdatedProducts TO CustomDBReader;
-- 4. 将用户加入自定义角色，同时移除db_datareader角色
ALTER ROLE db_datareader DROP MEMBER [peter@xxxxxx.co.uk];
ALTER ROLE CustomDBReader ADD MEMBER [peter@xxxxxx.co.uk];

方案B：保留db_datareader，强制阻断目标表权限

如果必须保留db_datareader角色，确保DENY权限正确生效：

-- 先撤销可能的直接授权（如果之前给过）
REVOKE ALL ON dbo.UpdatedProducts TO [peter@xxxxxx.co.uk];
-- 强制拒绝所有操作（SELECT/INSERT/UPDATE/DELETE）
DENY SELECT, INSERT, UPDATE, DELETE ON dbo.UpdatedProducts TO [peter@xxxxxx.co.uk];

执行后让用户重新连接数据库（旧连接可能缓存权限），再测试访问。

4. SSMS图形化操作注意事项 #

如果用SSMS设置权限：

• 右键目标表 → 属性 → 权限
• 选择目标用户peter@xxxxxx.co.uk，在显式权限中：
  • 找到Select、Insert等权限，勾选拒绝
  • 确保不要勾选授予或授予权限
• 点击确定后，刷新用户连接再测试

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