3. 数据库用户与架构

在 SQL Server 的安全模型中，登录名（Login）解决的是“能不能连上实例”的问题，而数据库用户（User）解决的是“连上之后在某个数据库里是谁”的问题。两者处于不同层级：登录名是服务器级主体，存放在 master 数据库中；数据库用户是数据库级主体，存放在各自数据库内。一个登录名可以在多个数据库中分别映射为不同的用户，也可以在某些数据库中没有对应用户——此时该登录名能连上实例，却无法访问那些数据库。

理解这一点的关键在于：登录名与数据库用户之间不是一对一，而是一对多。很多权限异常，根源都在于“登录名存在，但目标数据库里没有对应用户”，或者“用户存在，但它的 SID 与登录名不匹配”。

默认架构：用户解析对象名的第一站

当用户执行 SELECT * FROM Orders 这类不带架构前缀的语句时，SQL Server 需要确定去哪个架构下找 Orders 表。这个“第一站”就是该用户的默认架构（Default Schema）。如果默认架构是 sales，那么 Orders 会被解析为 sales.Orders；如果默认架构是 dbo，则解析为 dbo.Orders。

默认架构的设定有两种方式：一是在创建用户时通过 DEFAULT_SCHEMA 指定，二是后续用 ALTER USER 修改。若未显式指定，SQL Server 会默认使用 dbo 作为该用户的默认架构。这里有一个容易被忽视的陷阱：如果用户没有对 dbo 架构的访问权限，而默认架构又是 dbo，那么即使该用户在某个自定义架构下拥有表，不带前缀的查询也会失败。因此，为业务用户显式指定与其职责匹配的默认架构，是权限治理中的基本动作。

-- 创建带默认架构的用户
CREATE USER app_user FOR LOGIN app_login
    WITH DEFAULT_SCHEMA = sales;

-- 修改已有用户的默认架构
ALTER USER app_user WITH DEFAULT_SCHEMA = sales;

-- 查看数据库中所有用户及其默认架构
SELECT name, default_schema_name, type_desc
FROM sys.database_principals
WHERE type IN ('S', 'U', 'G')
ORDER BY name;

架构所有权：谁拥有这个命名空间

架构不仅是对象的命名空间，它本身也是一个可被拥有的安全对象。架构的所有者（Owner）对该架构下的对象拥有隐含的控制权。在 SQL Server 2005 之前，架构和用户是绑定的，删除用户会导致架构及其对象丢失；从 2005 起，架构与用户分离，架构可以独立存在并被不同用户拥有。

架构所有权的设计原则是：不要让普通业务用户成为架构所有者。如果 app_user 同时是 sales 架构的所有者，那么它就能对 sales 下的所有对象执行任意 DDL 操作，包括删除表、修改存储过程。更合理的做法是让 dbo 或一个专用的管理账号拥有架构，业务用户仅被授予该架构下的 SELECT、EXECUTE 等具体权限。

-- 将架构所有权转移给 dbo
ALTER AUTHORIZATION ON SCHEMA::sales TO dbo;

-- 查看架构及其所有者
SELECT s.name AS schema_name, p.name AS owner_name
FROM sys.schemas s
JOIN sys.database_principals p ON s.principal_id = p.principal_id
ORDER BY s.name;

用户与架构分离设计

把用户和架构分开设计，能带来三个实际好处。第一，权限边界清晰：权限授予架构而非单个对象，新增表时只需确保它属于正确架构，权限自动继承。第二，用户可替换：当人员离职或系统账号轮换时，只需删除旧用户、创建新用户并映射到同一登录名，架构和权限不受影响。第三，避免 dbo 泛滥：所有对象都放在 dbo 下会让权限控制退化为“要么全有要么全无”，按业务域划分架构（如 sales、hr、rpt）才能实现最小权限。

一个推荐的设计模式是：每个业务域一个架构，架构所有者统一为 dbo，业务用户拥有自己的默认架构，权限通过角色授予架构级别。例如：

-- 创建架构，所有者设为 dbo
CREATE SCHEMA sales AUTHORIZATION dbo;
CREATE SCHEMA hr AUTHORIZATION dbo;

-- 创建角色并授予架构级权限
CREATE ROLE sales_reader;
GRANT SELECT ON SCHEMA::sales TO sales_reader;

-- 将用户加入角色
ALTER ROLE sales_reader ADD MEMBER app_user;

常见错误与处理

错误一：登录名能连接但访问数据库报“无法打开数据库”或“对象名无效”。 原因通常是目标数据库中没有对应用户。处理方法是在该数据库中执行 CREATE USER [domain\login] FOR LOGIN [domain\login]，或使用 ALTER USER ... WITH LOGIN = ... 修复映射。

错误二：还原数据库后用户变成孤立用户（Orphaned User）。 还原到另一台服务器时，数据库内的用户 SID 与目标服务器上的登录名 SID 不一致，导致登录名无法映射到用户。诊断方法是查询 sys.database_principals 与 sys.server_principals 的 SID 差异，修复使用 ALTER USER [user] WITH LOGIN = [login] 重新建立映射。

-- 查找孤立用户
SELECT dp.name AS db_user, dp.sid AS user_sid
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type IN ('S', 'U')
  AND sp.sid IS NULL;

-- 修复映射
ALTER USER app_user WITH LOGIN = app_login;

错误三：默认架构指向不存在的架构。 如果创建用户时指定的 DEFAULT_SCHEMA 尚未创建，用户仍可创建，但后续不带前缀的查询会失败。处理方法是先创建架构，再创建用户，或用 ALTER USER 修正默认架构。

总结来说，登录名管“进门”，用户管“在屋里是谁”，默认架构管“先看哪个房间”，架构所有权管“谁有房间钥匙”。把这四者的关系理清，并在设计时坚持用户与架构分离、权限授予架构而非对象、架构所有者统一为 dbo，就能避免绝大多数权限治理中的常见故障。