SQL Server如何为指定角色授予特定架构的创建权限？

问题出在哪？ #

1. 未限制全局默认权限：你只给Role A配置了授权，但没回收public角色的相关创建权限——SQL Server中public角色会被所有用户默认继承，如果public拥有CREATE TABLE等数据库级权限，任何登录名（包括SQL Login B）都能在有权限的架构下创建对象。
2. 未做权限隔离配置：你完全没处理Schema B和Role B的权限规则，也没明确禁止Role B/SQL Login B访问Schema A的权限。
3. 权限逻辑不完整：要在架构内创建对象，需要同时具备数据库级的CREATE权限和对应架构的ALTER权限，你之前的语句只给Role A加了数据库级权限，没明确加固Schema A的权限边界。

正确的权限配置步骤 #

执行以下SQL语句完成严格的权限隔离：

1. 回收public角色的全局创建权限，消除默认权限隐患：

REVOKE CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE SCHEMA FROM PUBLIC;

2. 配置Schema A与Role A的专属权限：

-- 确认Schema A的所有权归属Role A
ALTER AUTHORIZATION ON SCHEMA::[Schema A] TO [Role A];
-- 授予Role A在Schema A的ALTER权限（创建对象的必要条件）
GRANT ALTER ON SCHEMA::[Schema A] TO [Role A];
-- 授予Role A数据库级的创建权限
GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE TO [Role A];

3. 配置Schema B与Role B的专属权限：

-- 确认Schema B的所有权归属Role B
ALTER AUTHORIZATION ON SCHEMA::[Schema B] TO [Role B];
-- 授予Role B在Schema B的ALTER权限
GRANT ALTER ON SCHEMA::[Schema B] TO [Role B];
-- 授予Role B数据库级的创建权限
GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE TO [Role B];

4. 加固权限隔离边界：确保SQL Login B不属于Role A，且Role B没有Schema A的任何权限：

-- 检查SQL Login B的角色成员关系
EXEC sp_helplogins 'SQL Login B';
-- 回收Role B对Schema A的所有权限（若存在）
REVOKE ALL ON SCHEMA::[Schema A] FROM [Role B];

验证效果 #

用SQL Login B登录后，尝试在Schema A创建对象会收到权限拒绝错误；在Schema B内则可正常执行创建操作。

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