SQL Server 2017限制public角色权限后指定登录无法访问数据库问题

问题根因 #

你之前执行的REVOKE语句仅回收了数据库级别的public权限，而SQL Server默认给public角色的SELECT/INSERT/DELETE等操作权限是绑定在schema级别（比如默认的dbo schema）授予的，两类权限互相独立，因此仅回收数据库级权限不会清除public持有的schema级授权，查询时自然还能看到对应权限记录。
另外此前给public设置的DENY权限优先级高于所有显式授权，必须先清除才不会影响你指定账号的权限生效。

解决步骤 #

1. 清理此前给public设置的所有DENY权限 #

先执行以下语句清除所有public的无效DENY配置，避免权限被拦截：

REVOKE ALL ON DATABASE::Foo TO PUBLIC CASCADE;

2. 批量回收public的所有schema级权限 #

执行以下脚本遍历数据库下的所有用户schema，回收public在所有schema下的操作权限：

DECLARE @schemaName NVARCHAR(128)
DECLARE schema_cursor CURSOR FOR
-- 可根据需求调整过滤条件，只处理你需要回收权限的schema
SELECT name FROM sys.schemas WHERE name NOT IN ('sys', 'INFORMATION_SCHEMA')
OPEN schema_cursor
FETCH NEXT FROM schema_cursor INTO @schemaName
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC('REVOKE SELECT, INSERT, UPDATE, DELETE, EXECUTE, REFERENCES, VIEW DEFINITION ON SCHEMA::' + @schemaName + ' TO PUBLIC CASCADE')
    FETCH NEXT FROM schema_cursor INTO @schemaName
END
CLOSE schema_cursor
DEALLOCATE schema_cursor

如果还有public针对单独表/存储过程等对象的显式授权，可以用你提供的query1筛选pr.name = 'public'和state_desc = 'GRANT'找到对应对象，单独执行REVOKE语句回收即可。

3. 给指定账号授予全库权限 #

确认你要授权的登录名已经映射为当前数据库的用户（假设映射后的数据库用户名为TargetUser），执行以下语句授予全库权限：

-- 方式1：加入db_owner角色，直接拥有全库所有权限
ALTER ROLE db_owner ADD MEMBER TargetUser;

-- 方式2：如果不想授予内置角色权限，可直接授予CONTROL权限
-- GRANT CONTROL ON DATABASE::Foo TO TargetUser WITH GRANT OPTION;

验证配置 #

配置完成后再次执行你提供的query1筛选public角色的权限，确认没有state_desc = 'GRANT'的记录即可。此时除了你的指定账号外，其余所有未单独授权的用户都无法访问该数据库，符合你的需求。

注：SQL Server不支持将用户从public角色中移除，因此该方案是通过回收public所有权限+单独给指定用户授权的方式实现需求，逻辑完全等效。

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