企业官网建设流程全解析

1. 为什么“加个用户”这件事值得单独写一篇

在 SQL Server 2008 R2 上给一个新同事开账号，看起来是 DBA 日常里最不起眼的一件小事：建个登录名、给个库的权限、让他能连上跑查询，五分钟收工。但我见过太多环境里，这一步做得稀里糊涂——有人直接把人加进sysadmin角色图省事，有人只建了服务器登录名却忘了建数据库用户，结果对方连上实例后一脸茫然地报“无法打开登录所请求的数据库”，还有人把权限给到了db_owner，事后审计时被追问“这个账号为什么能删表”答不上来。

这篇内容就是围绕SQL Server 2008 R2 中新增用户并设置访问权限这个具体场景展开的。它适合三类人：刚接手老版本 SQL Server 的运维、需要给业务方或数据分析同事开只读账号的开发、以及在做权限梳理和最小权限改造的负责人。我会把“登录名（Login）—数据库用户（User）—角色（Role）—具体权限（Permission）”这条链路拆开讲清楚，把每一步为什么这么做、不这么做会出什么问题说明白，最后给出一套可以直接照着敲的脚本和几个我踩过的坑。

需要先明确一个概念前提，这是整篇文章的地基：SQL Server 的权限体系是两层的。第一层在实例（服务器）级别，管的是“你能不能连上这台服务器”，载体是登录名；第二层在数据库级别，管的是“你连上之后能在某个库里干什么”，载体是数据库用户。登录名和数据库用户之间通过SID建立映射关系。很多人出问题，就是因为把这两层混为一谈，以为建了登录名就万事大吉。2008 R2 这套模型和后来的版本基本一致，所以这里讲清楚，往后迁移也能用得上。

2. 登录名、用户、角色、权限：四层概念先理清再动手

2.1 登录名和数据库用户不是一回事

我用一个生活化的类比来解释：登录名像是小区大门的门禁卡，证明你是这个小区的人，能进大门；数据库用户像是你家那栋楼、那套房子的钥匙，决定你进了小区之后能进哪栋楼、开哪扇门。你有了门禁卡但没配钥匙，进了小区也只能在楼下干站着——这就是“能连上实例但访问不了目标库”的典型症状。

在 SSMS（SQL Server Management Studio）的对象资源管理器里，这两个东西的位置也不一样：登录名在“安全性”节点下的“登录名”里，属于实例级；数据库用户在具体某个数据库节点下的“安全性”→“用户”里，属于库级。新建登录名时，SSMS 的“用户映射”页可以顺手把对应的数据库用户一起建出来，这是最省事的做法，但很多人只填了“常规”页就点了确定，映射页空着，问题就埋下了。

还有一个容易忽略的点：登录名和数据库用户是靠 SID 关联的，不是靠名字。如果你在 A 库建了用户zhangsan，后来把库还原到另一台服务器，而那台服务器上恰好也有个叫zhangsan的登录名，但 SID 对不上，就会出现“孤立用户”——名字看着对，权限却挂不上。这个坑在数据库迁移场景里极其常见，后面我会单独讲怎么修。

2.2 服务器角色和数据库角色的分工

角色是权限的“打包发放”。服务器级别有固定服务器角色，比如sysadmin（全权）、securityadmin（管登录名和权限）、dbcreator（建库）等；数据库级别有固定数据库角色，比如db_owner（库内全权）、db_datareader（只读所有表）、db_datawriter（写所有表）、db_denydatareader（拒绝读）等。

这里有个我反复强调的原则：能用固定角色解决的，就别一个个授具体权限；但固定角色给大了，就宁可拆开授具体权限。比如给一个只做报表查询的同事，db_datareader就够了，别给db_owner；如果业务只需要读某几张表而不是全库，那就别用db_datareader，改成对具体表授SELECT。最小权限不是口号，是审计时能拿出证据的东西。

下表是我常用的角色选择对照，按典型岗位场景整理：

2.3 权限的授予、拒绝与继承关系

SQL Server 的权限有三种状态：GRANT（授予）、DENY（拒绝）、REVOKE（收回）。很多人以为REVOKE和DENY是一回事，其实差别很大：REVOKE是把之前授的权限撤掉，回到“没授也没拒”的中间状态，此时用户可能通过角色继承拿到权限；而DENY是明确拒绝，DENY 的优先级高于任何 GRANT，哪怕用户通过角色被授予了权限，只要在某个层级被 DENY，就真的用不了。

这个特性在排查“明明给了权限却用不了”的问题时特别关键。我遇到过一次：某账号通过db_datareader能读全库，但某张敏感表被单独DENY SELECT，结果他查那张表就报错，而查其他表正常。当时排查的人一直以为是角色没生效，其实是 DENY 在起作用。所以做权限设计时，尽量用“不授予”代替“显式拒绝”，除非你确实需要覆盖继承来的权限。

3. 用 SSMS 图形界面走一遍完整流程

3.1 新建登录名的两种认证方式怎么选

打开 SSMS 连上实例，展开“安全性”，右键“登录名”→“新建登录名”。第一个要做的选择是认证方式：Windows 身份验证还是SQL Server 身份验证。

Windows 身份验证走的是域账号或本机账号，密码策略由操作系统管，适合企业内部员工、域环境下的运维和开发。SQL Server 身份验证是数据库自己管账号密码，适合应用连接、跨域访问、或者没有域环境的小型部署。我的经验是：人能用人脸/域账号的，优先 Windows 验证；应用连接、自动化脚本用 SQL 验证。混用不是不行，但账号来源越杂，后期审计越麻烦。

选 SQL Server 身份验证时，要填登录名和密码，下面有几个复选框值得注意：

• 强制实施密码策略：默认勾选，会套用系统的密码复杂度、过期策略。老系统里如果应用不支持定期改密，这个勾可能要取消，但要清楚这是降低了安全性。
• 强制密码过期：配合上一条，应用账号一般取消，避免某天突然连不上。
• 用户在下次登录时必须更改密码：给真人首次登录用，应用账号别勾。

提示：给应用使用的 SQL 登录名，建议单独建、单独命名（比如带app_前缀），不要和真人账号混在一起，方便后续按前缀批量审计和禁用。

3.2 “用户映射”页才是关键一步

填完“常规”页，切到左侧的“用户映射”。这里上半部分是“映射到此登录名的用户”，勾选目标数据库，右边会自动带出“用户”名（默认和登录名相同，可以改）和“默认架构”（一般填dbo）。下半部分是“数据库角色成员身份”，勾选这个用户在所选库里属于哪些角色。

这一步是整个流程里最容易漏的。如果你只建了登录名没勾映射，用户能连上实例，但一执行USE 目标库就报错。我建议的做法是：在映射页里先只勾public，其他角色先不勾，等登录名建好、连通性验证通过之后，再单独回来按需加角色。这样出问题时能快速定位是“连不上”还是“权限不够”，两个问题分开排查，效率高很多。

“默认架构”这个字段也值得说一句。如果填了dbo，用户建表时不带架构名就默认建在dbo下；如果留空或者填了别的，可能出现“对象建了但查不到”的怪现象——因为对象建在了用户同名的架构下，而查询时按默认架构解析。对普通业务账号，统一填dbo最省心。

3.3 用“安全对象”页做精细化授权

如果固定角色满足不了需求，比如只想让某账号读三张表、执行两个存储过程，就要用登录名属性里的“安全对象”页，或者直接在数据库里对用户授具体权限。图形界面的操作路径是：右键数据库→“属性”→“权限”，或者右键具体表→“属性”→“权限”。

图形界面适合一次性、少量的授权，但批量、可复现的授权我强烈建议用 T-SQL 脚本，原因很简单：脚本能存进版本库、能 review、能在测试环境重放、出问题能回滚。图形界面点出来的东西，过两个月你自己都记不清当时点了哪些勾。下一节我把整套脚本给出来。

4. 一套可直接复用的 T-SQL 脚本模板

4.1 建登录名与数据库用户的标准写法

下面这套脚本是我在多个环境里用下来最稳的模板，按“建登录名→建用户→授角色→验证”的顺序组织。先看建登录名和用户的部分：

-- 1. 在 master 库上下文创建服务器级登录名 USE master; GO -- SQL Server 身份验证登录名（应用账号示例） CREATE LOGIN app_report_user WITH PASSWORD = '换成你的强密码', CHECK_POLICY = ON, CHECK_EXPIRATION = OFF, DEFAULT_DATABASE = [TargetDB]; GO -- 2. 切换到目标库，创建数据库用户并映射到上面的登录名 USE [TargetDB]; GO CREATE USER app_report_user FOR LOGIN app_report_user WITH DEFAULT_SCHEMA = dbo; GO

几个参数的选择理由：CHECK_POLICY = ON保留密码复杂度检查，这是安全基线；CHECK_EXPIRATION = OFF是因为应用账号不适合定期强制改密，否则某天凌晨任务就挂了；DEFAULT_DATABASE指定默认库，用户登录后不写USE也能直接落到目标库，减少“对象找不到”的误报。

如果目标库已经存在同名用户但映射错了（比如从别的服务器还原过来的孤立用户），CREATE USER会报错。这时候要用ALTER USER ... WITH LOGIN = ...重新绑定 SID，或者先DROP USER再重建。孤立用户的排查和修复我在第 5 节详细讲。

4.2 按场景授角色或具体权限

建好用户后，按实际需求授权。只读报表场景：

-- 方案 A：整库只读（简单，但范围大） USE [TargetDB]; GO ALTER ROLE db_datareader ADD MEMBER app_report_user; GO -- 方案 B：只读指定表（最小权限，推荐） GRANT SELECT ON dbo.OrderHeader TO app_report_user; GRANT SELECT ON dbo.OrderDetail TO app_report_user; GO

需要读写业务表的应用账号：

USE [TargetDB]; GO ALTER ROLE db_datareader ADD MEMBER app_report_user; ALTER ROLE db_datawriter ADD MEMBER app_report_user; GO

需要执行存储过程但不直接碰表的账号（这种设计其实更安全，业务逻辑封装在存储过程里）：

GRANT EXECUTE ON SCHEMA::dbo TO app_report_user; GO

注意GRANT EXECUTE ON SCHEMA::dbo是给整个dbo架构下所有存储过程执行权限，范围偏大；更严格的做法是对具体存储过程逐个GRANT EXECUTE ON OBJECT::dbo.usp_GetReport TO ...。选哪个取决于你的存储过程是否都适合这个账号调用。

4.3 授权后的验证脚本

授完权别急着交付，跑一遍验证。我常用的验证思路是“用目标账号自己查自己”：

-- 以目标登录名身份执行，验证能连、能读 EXECUTE AS LOGIN = 'app_report_user'; GO SELECT DB_NAME() AS CurrentDB; SELECT TOP 5 * FROM dbo.OrderHeader; GO REVERT; GO

EXECUTE AS LOGIN会模拟该登录名的上下文，如果权限没配好，这里就会直接报错，比让对方去试快得多。验证完记得REVERT切回自己的上下文，否则后续操作都在模拟身份下执行，容易出乱子。

再补一个查权限的脚本，交付前自己核对一遍：

-- 查某用户在当前库的有效权限 SELECT pr.name AS PrincipalName, pe.state_desc AS PermissionState, pe.permission_name AS PermissionName, pe.class_desc AS ObjectClass, OBJECT_NAME(pe.major_id) AS ObjectName FROM sys.database_permissions pe JOIN sys.database_principals pr ON pe.grantee_principal_id = pr.principal_id WHERE pr.name = 'app_report_user'; GO

这个查询能列出直接授予的权限，但不包含通过角色继承来的权限。要查继承权限，得结合sys.fn_my_permissions或者用EXECUTE AS实测，这也是为什么我强调“验证要用模拟身份实跑”，光看权限表容易漏掉角色继承的部分。

5. 孤立用户、连接失败、权限不生效：三个高频坑的排查链路

5.1 数据库还原后用户“失联”的根因与修复

这是 2008 R2 环境里最高频的问题之一。场景：生产库备份还原到测试服务器，测试服务器上已经建了同名登录名，但还原过来的数据库用户还带着原服务器的 SID，和本地登录名对不上，于是用户成了“孤立用户”——在库里能看到这个用户，但它没有对应的登录名，谁也登不进去。

排查链路是这样的：先在目标库跑下面这句，看sid和type：

USE [TargetDB]; GO SELECT name, sid, type_desc, authentication_type_desc FROM sys.database_principals WHERE type IN ('S','U') AND name = 'app_report_user'; GO

再在 master 里查登录名的 SID：

SELECT name, sid FROM sys.sql_logins WHERE name = 'app_report_user';

两个 SID 不一致，就是孤立用户。修复用sp_change_users_login（2008 R2 里还在，虽然官方标记为弃用但能用）：

USE [TargetDB]; GO -- 先看报告，确认哪些是孤立的 EXEC sp_change_users_login 'Report'; GO -- 把孤立用户重新映射到同名登录名 EXEC sp_change_users_login 'Auto_Fix', 'app_report_user'; GO

Auto_Fix会按名字匹配登录名并重建映射。如果名字对不上（比如登录名叫app_report_user_new），就用Update_One显式指定。修完之后再跑一次Report确认列表清空。这个坑的预防办法是：还原脚本里固定加一步孤立用户检查和修复，别等业务方报错才想起来。

5.2 “登录失败”和“无法打开数据库”要分开查

用户连不上，报错信息其实已经把方向指得很清楚了，但很多人不看错误号直接瞎试。我按错误号整理了一张对照表：

18456 这个错误，SSMS 里只显示“登录失败”，但服务器错误日志里会有更详细的状态码，比如状态 8 表示密码不匹配，状态 7 表示账号被禁用且密码错误，状态 38 表示数据库不存在。养成看错误日志的习惯，能省掉一半的猜测时间。

4060 则几乎总是映射问题：要么登录名的默认库指向了一个该用户没有权限的库，要么用户映射没建。修复办法是改默认库或者补映射，别去动密码。

5.3 权限给了却不生效的三种可能

第一种，DENY 覆盖了 GRANT。前面讲过，DENY 优先级最高，查一下sys.database_permissions里有没有state_desc = 'DENY'的记录。

第二种，授错了主体。比如给角色授了权限，但用户不在那个角色里；或者给一个同名但不同 SID 的用户授了权。用EXECUTE AS实测最直接。

第三种，架构归属问题。用户对表有 SELECT 权限，但表的架构是sales而不是dbo，而用户没有sales架构的SELECT权限，照样查不了。这种情况在从别的系统迁移过来的库上很常见，因为对象分散在多个自定义架构下。解决办法是按架构授权：GRANT SELECT ON SCHEMA::sales TO ...，或者把对象统一归到dbo。

6. 权限治理的几条实操心得

6.1 账号命名和归属要能一眼看懂

我经手过的环境里，最头疼的不是权限配错，而是根本不知道某个账号是谁在用。user1、test、sqladmin这种名字，过半年没人说得清。我的做法是强制命名规范：真人账号用域账号或姓名拼音，应用账号用app_业务名_用途，临时账号用tmp_用途_到期日。同时在登录名的“注释”或者一张维护表里记录归属人、申请时间、用途、到期时间。

临时账号尤其要有到期意识。给外部顾问开个只读账号，说好用两周，结果一年后还在，这种事太常见了。我的习惯是建账号时就在日历上记一笔到期提醒，到期没续期就禁用。禁用比删除好，因为删除后如果对方还在用，报错信息不直观；禁用后连接报 18456，一看日志就知道是账号被禁，沟通成本低。

6.2 定期做权限盘点，别等审计

权限是会“长毛”的：项目上线时给的大权限，项目结束后没人收回；临时提权排查问题，排查完忘了降回去。我建议至少每季度跑一次盘点，重点看三类账号：属于sysadmin的、属于db_owner的、长期未登录的。

查sysadmin成员：

SELECT p.name, p.type_desc, p.create_date, p.modify_date FROM sys.server_role_members rm JOIN sys.server_principals r ON rm.role_principal_id = r.principal_id JOIN sys.server_principals p ON rm.member_principal_id = p.principal_id WHERE r.name = 'sysadmin';

查库内db_owner成员：

USE [TargetDB]; GO SELECT p.name, p.type_desc FROM sys.database_role_members rm JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id JOIN sys.database_principals p ON rm.member_principal_id = p.principal_id WHERE r.name = 'db_owner';

查长期未登录的（依赖登录时间的记录，2008 R2 里可以查sys.dm_exec_sessions的当前会话，历史登录时间需要靠登录审计或者自己维护表）：

SELECT login_name, MAX(login_time) AS LastLogin FROM sys.dm_exec_sessions GROUP BY login_name;

这个 DMV 只反映当前实例上的会话，实例重启就清空了，所以长期登录审计要靠开启登录审计或者自己建一张记录表，别指望这个视图给你历史数据。这一点很多人不知道，以为查这个就能看到“三个月没登录的账号”，其实查不到。

6.3 脚本化、版本化，别靠记忆

最后一条也是最重要的一条：所有账号和权限变更都走脚本，脚本进版本库。图形界面点出来的变更，没法 review、没法回滚、没法在灾备环境重放。我现在的习惯是每个账号一个建号脚本，文件头写清楚申请人、用途、到期日，变更时改脚本、走 review、再执行。听起来麻烦，但真出问题时，你能在五分钟内说清楚“这个账号是什么时候、因为什么、被谁加的什么权限”，这个价值远超那点多花的工夫。

脚本模板可以统一成“幂等”写法，重复执行不报错，方便在多个环境跑：

-- 幂等建登录名：存在则跳过 IF NOT EXISTS (SELECT 1 FROM sys.sql_logins WHERE name = 'app_report_user') BEGIN CREATE LOGIN app_report_user WITH PASSWORD = '换成你的强密码', CHECK_POLICY = ON, CHECK_EXPIRATION = OFF, DEFAULT_DATABASE = [TargetDB]; END GO -- 幂等建用户 USE [TargetDB]; GO IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'app_report_user' AND type = 'S') BEGIN CREATE USER app_report_user FOR LOGIN app_report_user WITH DEFAULT_SCHEMA = dbo; END GO

幂等脚本的好处是，无论环境当前是什么状态，跑一遍就能收敛到目标状态，不用先判断“这个环境建过没有”。配合版本库，新环境初始化、灾备切换、权限回滚都能复用同一套脚本。

我在实际使用中最大的体会是：SQL Server 2008 R2 这套权限模型本身不复杂，复杂的是人和流程。技术上的坑，查错误号、看系统视图基本都能定位；真正难的是让每个账号都有明确的归属、每个权限都有明确的理由、每次变更都有据可查。把脚本和命名规范这两件事坚持下来，后面九成的权限问题都不会发生。