如何在Azure SQL中彻底撤销用户对指定Schema的访问权限？

核心原因 #

你执行的REVOKE SELECT仅撤销了数据查询权限，但用户能看到Schema的本质是拥有该Schema的VIEW DEFINITION权限——这可能是通过DB Role继承的，或是数据库默认角色（如public）的权限。另外，SQL Server/Azure SQL会缓存用户登录时的权限会话，用户移除AD组后未重新登录的话，旧权限缓存仍会生效。

彻底撤销权限的步骤 #

1. 撤销DB Role对目标Schema的全量权限 #

需要明确撤销包括元数据查看在内的所有权限，而非仅数据操作权限：

-- 撤销数据操作类权限
REVOKE SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: [Schema_Name] TO [DB Role];
-- 撤销Schema定义查看权限
REVOKE VIEW DEFINITION ON SCHEMA :: [Schema_Name] TO [DB Role];
-- 若曾授予过修改权限，一并撤销
REVOKE ALTER ON SCHEMA :: [Schema_Name] TO [DB Role];

2. 检查并清理直接权限 #

部分场景下可能直接给AD组对应的DB User授予了权限（而非通过Role），需先排查：

-- 查询目标Schema的所有权限分配
SELECT dp.permission_name, dp.state_desc, sp.name AS grantee_name
FROM sys.database_permissions dp
JOIN sys.database_principals sp ON dp.grantee_principal_id = sp.principal_id
WHERE dp.major_id = SCHEMA_ID('Schema_Name');

若发现存在直接授予AD组DB User的权限，执行对应撤销命令：

REVOKE ALL ON SCHEMA :: [Schema_Name] TO [AD组对应的DB User名称];

3. 强制用户重新登录 #

用户的权限会话在登录时生成，移除AD组后必须让用户退出并重新登录，新的权限限制才会生效。

4. 可选：移除DB Role中的AD组用户（若后续无需关联） #

如果该AD组不再需要访问数据库，可直接将对应的DB User从DB Role中移除：

EXEC sp_droprolemember @rolename = '[DB Role]', @membername = '[AD组对应的DB User名称]';

5. 清理前的权限确认 #

在清理Schema前，务必确认无有效权限残留：

-- 检查目标Schema的权限记录
SELECT * FROM sys.database_permissions WHERE major_id = SCHEMA_ID('Schema_Name');
-- 检查DB Role的成员列表
SELECT sp.name AS member_name
FROM sys.database_role_members drm
JOIN sys.database_principals sp ON drm.member_principal_id = sp.principal_id
WHERE drm.role_principal_id = USER_ID('[DB Role]');

额外排查点 #

数据库默认角色public可能拥有部分元数据查看权限，若用户仍能看到Schema，需检查并撤销：

REVOKE VIEW DEFINITION ON SCHEMA :: [Schema_Name] TO public;

另外，Azure AD组权限同步可能存在数分钟延迟，移除用户后需等待片刻再让用户重新登录。

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