如何配置SQL Server架构仅允许指定数据库角色访问

首先明确前提：sysadmin固定服务器角色成员、数据库所有者（dbo）本身会跳过SQL Server的常规权限校验，天然拥有全库所有对象的访问权限，不需要额外配置，我们只需要做好其他所有主体的权限封堵，再单独给目标角色开放权限即可。

• 第一步：修正架构所有权，避免权限旁路
  架构所有者默认拥有对应架构的全部操作权限，如果[TopSecret]的所有者不是dbo，会导致普通账号拿到不受控的全权限，先执行检查和修正：

  -- 检查架构当前所有者
  SELECT name, USER_NAME(principal_id) AS owner_name 
  FROM sys.schemas 
  WHERE name = N'TopSecret';
  
  -- 如果返回的owner_name不是dbo，执行以下语句修正所有者
  ALTER AUTHORIZATION ON SCHEMA::[TopSecret] TO dbo;
  

• 第二步：清除public角色的默认架构权限
  所有数据库用户默认都属于public数据库角色，必须先撤销该角色在[TopSecret]架构上的所有默认权限，从根源上封堵普通用户的默认访问路径：

  REVOKE ALL ON SCHEMA::[TopSecret] TO public;
  

  关键避坑：绝对不要对public角色执行DENY操作。SQL Server中DENY权限优先级高于GRANT，如果给public加了拒绝规则，后续就算给[CLevel]单独授权，角色成员也会因为属于public组被拒绝访问。

• 第三步：清理其他遗留授权
  如果你之前给过其他自定义数据库角色、个别单独用户授予过[TopSecret]架构的权限，需要逐一撤销这类授权，避免非授权用户通过遗留权限访问。可以用下面的语句查询当前所有对该架构有权限的主体：

  -- 查询所有拥有TopSecret架构权限的主体
  SELECT 
      USER_NAME(dp.grantee_principal_id) AS principal_name,
      dp.permission_name,
      dp.state_desc
  FROM sys.database_permissions dp
  WHERE dp.class = 3 -- 类3对应架构级权限
    AND OBJECT_NAME(dp.major_id) = N'TopSecret';
  

  对查询结果里除了dbo、CLevel之外的主体，执行撤销语句即可，格式参考：

  -- 替换成实际要撤销权限的主体名
  REVOKE ALL ON SCHEMA::[TopSecret] TO [替换为非授权的用户名/角色名];
  

• 第四步：给[CLevel]角色授予所需权限
  根据实际业务需要给角色授予对应权限即可，如果需要角色成员能对架构下所有对象做增删改查、执行存储过程等全操作，直接授予全权限：

  GRANT ALL ON SCHEMA::[TopSecret] TO CLevel;
  

  如果只需要只读权限，就替换为GRANT SELECT ON SCHEMA::[TopSecret] TO CLevel;，需要其他细粒度权限（比如INSERT、UPDATE、DELETE、EXECUTE）按需追加即可。
• 第五步：权限验证
  配置完成后用两个测试账号校验：一个加入[CLevel]角色，确认可以正常访问架构下的对象；另一个不加入角色，确认访问时会触发权限拒绝错误，就说明配置生效。

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