★ FEATURED ARTICLE 数据库权限管理这件事说大不大说小也不小。很多团队在项目初期图省事直接拿最高权限账号连库操作等到系统上线、外包人员进场、多部门共用一套实例的时候才发现权限边界模糊带来的麻烦远比想象中多。SQL Server 2008 R2 中新增用户并设置访问权限这个操作本质上解决的就是让正确的人只能看到正确的数据这个问题。它看起来只是几条语句的事但背后牵扯到登录名与数据库用户的区别、服务器级与数据库级两层权限模型、架构归属、角色继承等一系列概念稍不注意就会出现用户建了却连不上能连上却看不到表能看到表却改不了数据这类反复排查的尴尬局面。这篇内容面向的是需要在一套 SQL Server 2008 R2 实例上做权限隔离的开发和运维人员尤其是那些接手了历史系统、需要给新同事或外部协作方开账号的朋友。我会从整体设计思路讲起把两层权限模型掰开揉碎再给出可以直接照着敲的完整实操流程最后把我这些年踩过的坑和排查套路整理成速查表。即便你之前只写过CREATE LOGIN这一条语句看完也能把整套权限体系理清楚。SQL Server 2008 R2 虽然是个老版本但大量存量系统仍在跑它这套知识放到今天依然有很强的实用价值。1. 权限体系整体设计与思路拆解1.1 为什么 SQL Server 要分登录名和数据库用户两层刚接触 SQL Server 的人最容易困惑的一点是我明明建了一个账号为什么还要在数据库里再建一次这跟 MySQL 那种用户即账号、账号即权限的模型完全不同。理解这个差异是做好权限管理的第一步。打个生活化的比方。登录名Login相当于你进公司大楼的门禁卡它只负责回答一个问题你能不能进这栋楼。而数据库用户User相当于你在某个具体部门里的工牌它回答的是你进了楼之后能进哪个部门、能碰哪些文件。一栋楼里有多个部门多个数据库你有一张门禁卡但未必每个部门都给你发工牌。这个门禁卡 部门工牌的双层结构就是 SQL Server 权限模型的核心。这种设计的好处在于解耦。服务器级别的身份认证登录名和数据库级别的访问授权用户分开管理意味着同一个登录名可以在多个数据库里拥有不同的用户身份和不同的权限组合。比如某个开发账号在测试库里是读写权限在生产库里只有只读权限靠的就是这种分层。反过来如果某个登录名在某个数据库里根本没有对应的用户那它连这个库的门都进不去哪怕它在服务器级别权限再高sysadmin 除外这是特例后面会讲。注意登录名存在于实例级别master 库的 sys.server_principals 视图数据库用户存在于每个数据库内部各自库的 sys.database_principals 视图。两者通过 SID安全标识符关联而不是靠名字关联。这一点在数据库备份还原、跨实例迁移时特别关键名字一样但 SID 对不上权限就会失效。1.2 服务器角色与数据库角色的分工逻辑SQL Server 2008 R2 的权限授予是通过角色来组织的而不是直接往用户身上堆权限。角色分两大类服务器角色和数据库角色。理解它们的分工能帮你避免权限给多了不安全、给少了不够用的两难。服务器角色管的是实例级别的事比如能不能创建数据库、能不能管理登录名、能不能关机。常用的几个固定服务器角色如下表服务器角色能做什么适用场景sysadmin实例内最高权限无所不能仅限 DBA绝不外放serveradmin配置服务器级设置极少单独授予securityadmin管理登录名和权限权限管理员dbcreator创建、修改、删除数据库开发负责人bulkadmin执行批量导入数据导入岗位public每个登录名默认属于无需显式授予数据库角色管的是单个库内部的事比如能不能读表、能不能写数据、能不能执行存储过程。常用的固定数据库角色有db_owner、db_datareader、db_datawriter、db_ddladmin、db_backupoperator等。这里有个非常实用的组合给一个账号同时授予db_datareader和db_datawriter它就拥有了对库内所有用户表的读写能力但改不了表结构、删不了表这是业务系统最常见的权限配置。我个人的经验是能用固定角色解决的绝不自定义角色。固定角色的权限边界是微软设计好的经过大量场景验证不容易出岔子自定义角色虽然灵活但一旦设计不当很容易出现权限漏洞或者权限叠加导致的越权。只有在固定角色无法满足的精细化场景下比如只允许访问某几张表才考虑用自定义角色配合架构Schema来做隔离。1.3 方案选型的三个关键决策点在真正动手之前有三个决策点必须先想清楚否则后面会反复返工。第一个决策点是认证方式。SQL Server 2008 R2 支持 Windows 身份验证和 SQL Server 身份验证两种模式。如果实例配置的是混合模式两种都能用如果只配了Windows 身份验证模式那CREATE LOGIN ... WITH PASSWORD会直接报错。所以动手前先确认实例的认证模式用SELECT SERVERPROPERTY(IsIntegratedSecurityOnly)查一下返回 1 表示仅 Windows 认证返回 0 表示混合模式。这个检查能帮你省掉一次莫名其妙的报错。第二个决策点是权限粒度。是给整个库的读写权限还是只给某几张表是只读还是可写这直接决定了后面是用固定角色还是自定义角色。我的建议是遵循最小权限原则业务查询账号只给读业务写入账号只给写绝不给db_owner。很多安全事故的根源就是图省事给了过高权限。第三个决策点是默认架构。新建用户时如果不指定默认架构默认是dbo。这意味着用户查询表时如果不带架构前缀会优先在dbo架构下找。如果这个库里有多个业务架构比如sales、hr、finance默认架构设错了会导致用户明明有权限却查不到表。这个细节后面实操部分会重点讲。2. 核心细节解析与实操要点2.1 登录名创建密码策略与默认库的取舍创建登录名是整个流程的第一步语句本身不复杂但几个参数的选择很有讲究。标准写法是这样的USE master; GO CREATE LOGIN [app_reader] WITH PASSWORD Str0ngPassw0rd!2024, DEFAULT_DATABASE [BusinessDB], CHECK_POLICY ON, CHECK_EXPIRATION OFF; GO逐项拆解一下。PASSWORD后面的密码必须满足 Windows 密码策略如果CHECK_POLICY ON要求包含大小写字母、数字、符号中的至少三类长度一般不少于 8 位。CHECK_EXPIRATION OFF表示密码不过期这个在服务账号场景下是必须的否则密码到期后业务连接会突然中断排查起来非常痛苦。但如果是给真人使用的账号建议设为ON强制定期改密码。DEFAULT_DATABASE这个参数容易被忽视。它决定了用户登录后默认进入哪个库。如果不指定默认是master用户一登录就待在系统库里既不方便也有误操作风险。把它设成业务库用户体验会好很多。不过要注意如果这个默认库用户没有访问权限登录时会直接失败并报错无法打开默认数据库这是个很典型的坑后面排查部分会细说。实操心得给服务账号应用程序连接用的账号建登录名时我习惯把CHECK_POLICY设为ON但CHECK_EXPIRATION设为OFF。这样密码强度有保障又不会因为过期导致半夜告警。密码本身用密码管理器生成和保管绝不硬编码在配置文件里明文存放。2.2 数据库用户映射SID 关联的隐藏逻辑登录名建好后要在目标数据库里创建对应的用户。这里有两种写法效果一样但适用场景不同USE [BusinessDB]; GO -- 写法一按登录名映射最常用 CREATE USER [app_reader] FOR LOGIN [app_reader]; -- 写法二指定架构适合需要隔离的场景 CREATE USER [app_reader] FOR LOGIN [app_reader] WITH DEFAULT_SCHEMA [sales]; GO第一种写法最简单用户名和登录名同名默认架构是dbo。第二种写法显式指定了默认架构适合这个用户主要操作某个特定业务架构的场景。这里必须强调 SID 的问题。CREATE USER ... FOR LOGIN这条语句的本质是把数据库用户的 SID 设置为对应登录名的 SID。如果你用CREATE USER [app_reader] WITHOUT LOGIN建了一个孤立用户或者从别的实例还原数据库后用户失去了登录名映射就会出现孤儿用户——数据库里有这个用户但服务器上没有对应的登录名这个用户永远登录不上。修复孤儿用户的标准做法是ALTER USER [app_reader] WITH LOGIN [app_reader]它会重新按名字匹配并同步 SID。还有一种情况是数据库还原到另一台服务器后虽然两边都有同名登录名但 SID 不同用户依然连不上。这时候同样用ALTER USER ... WITH LOGIN重新映射即可。这个操作我在做数据库迁移时几乎每次都会用到属于必备技能。2.3 权限授予角色 vs 直接授权 vs 架构级授权权限授予有三种粒度从粗到细依次是角色级、架构级、对象级。选哪种取决于你的隔离需求。角色级最省事直接把用户加进固定数据库角色USE [BusinessDB]; GO -- 只读权限 ALTER ROLE [db_datareader] ADD MEMBER [app_reader]; -- 读写权限 ALTER ROLE [db_datareader] ADD MEMBER [app_writer]; ALTER ROLE [db_datawriter] ADD MEMBER [app_writer]; GO注意 SQL Server 2008 R2 用的是ALTER ROLE ... ADD MEMBER语法2005 之前是sp_addrolemember2008 之后两种都支持但推荐用ALTER ROLE。db_datareader给的是对库内所有用户表的 SELECT 权限db_datawriter给的是 INSERT、UPDATE、DELETE 权限。这两个角色组合起来就是标准的业务读写权限但注意它们不包含 EXECUTE 权限如果业务要调用存储过程还得额外授予。架构级授权适合多业务隔离的场景。假设库里有sales和hr两个架构你希望某个用户只能访问sales架构下的所有对象USE [BusinessDB]; GO GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::[sales] TO [app_sales_user]; GO这种写法的好处是以后sales架构下新增的表这个用户自动就有权限不用逐表授权。这是架构级授权相比对象级授权最大的优势——可维护性强。对象级授权最精细适合只开放个别表的场景USE [BusinessDB]; GO GRANT SELECT ON OBJECT::[dbo].[Orders] TO [app_report_user]; GRANT SELECT ON OBJECT::[dbo].[OrderDetails] TO [app_report_user]; GO对象级授权的缺点是维护成本高每加一张表就要补一条授权语句。所以我的原则是能用架构级就不用对象级除非确实只有零星几张表需要开放。2.4 拒绝优先原则DENY 的威力与陷阱SQL Server 的权限判断有个铁律DENY 优先于 GRANT。也就是说无论你通过多少个角色授予了权限只要在某个层级上有一条 DENY那个权限就被彻底封死。这个特性用好了是精细控制的利器用不好就是排查噩梦。举个例子。某个用户通过db_datareader角色拥有了全库读权限但你不希望他看Salary表USE [BusinessDB]; GO DENY SELECT ON OBJECT::[dbo].[Salary] TO [app_reader]; GO这样即使他在db_datareader角色里也读不了Salary表。但要注意如果这个用户同时属于db_owner角色DENY 对db_owner和sysadmin是无效的——这两个角色的权限太高DENY 压不住。所以做权限隔离时绝不能把需要限制的用户放进db_owner。注意DENY 一旦授予排查起来很隐蔽。因为sys.database_permissions视图里能看到但很多人排查权限问题时只看角色成员关系忽略了显式的 DENY。我遇到过好几次明明在角色里却查不了表的案例最后都是历史遗留的 DENY 在作祟。所以接手老系统时第一件事就是查一遍显式 DENY。3. 完整实操流程与关键环节实现3.1 从零开始的完整操作脚本下面这套脚本是我在实际项目中反复使用的标准流程从检查环境到验证权限一气呵成。假设场景是给一个报表查询账号rpt_user开通对BusinessDB库的只读权限且只能看sales架构下的表。第一步确认实例认证模式避免后面建登录名时报错-- 返回 0 表示混合模式可以建 SQL 登录名 SELECT SERVERPROPERTY(IsIntegratedSecurityOnly) AS IsWindowsOnly;第二步在 master 库创建登录名USE master; GO IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name rpt_user) BEGIN CREATE LOGIN [rpt_user] WITH PASSWORD Rpt2024#Secure, DEFAULT_DATABASE [BusinessDB], CHECK_POLICY ON, CHECK_EXPIRATION OFF; PRINT 登录名创建成功; END ELSE PRINT 登录名已存在跳过; GO这里用IF NOT EXISTS做幂等判断脚本可以重复执行不报错这在自动化部署里很重要。第三步在目标库创建用户并指定默认架构USE [BusinessDB]; GO IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name rpt_user) BEGIN CREATE USER [rpt_user] FOR LOGIN [rpt_user] WITH DEFAULT_SCHEMA [sales]; PRINT 数据库用户创建成功; END GO第四步授予架构级只读权限USE [BusinessDB]; GO GRANT SELECT ON SCHEMA::[sales] TO [rpt_user]; GO第五步验证权限是否生效。这一步很多人会跳过但恰恰是最重要的。验证分两个层面能不能连上、能不能查到数据。-- 验证用户映射关系 USE [BusinessDB]; GO SELECT dp.name AS DatabaseUser, sp.name AS ServerLogin, dp.default_schema_name AS DefaultSchema, dp.type_desc AS UserType FROM sys.database_principals dp LEFT JOIN sys.server_principals sp ON dp.sid sp.sid WHERE dp.name rpt_user; GO这条查询能一次性确认用户是否成功映射到登录名、默认架构是否正确。如果ServerLogin显示为 NULL说明是孤儿用户需要重新映射。3.2 用 EXECUTE AS 模拟用户身份做权限验证光看元数据还不够真正靠谱的验证方式是以该用户身份执行。SQL Server 提供了EXECUTE AS USER语法可以在不实际登录的情况下模拟用户权限USE [BusinessDB]; GO EXECUTE AS USER rpt_user; -- 测试能否查询 sales 架构下的表 SELECT TOP 10 * FROM sales.Orders; -- 测试能否查询 dbo 架构下的表应该失败 SELECT TOP 10 * FROM dbo.Salary; REVERT; GOREVERT用于恢复原始身份千万别忘了执行否则后续操作都会以模拟身份进行。这个技巧在权限调试时极其好用比真的退出重连快得多。我习惯在授权脚本末尾附上一段EXECUTE AS验证确认权限符合预期再交付。实操心得EXECUTE AS USER模拟的是数据库用户身份它不会重新做登录认证所以能验证数据库级权限但验证不了能不能登录这个问题。要验证登录还是得用实际的连接测试比如用 SSMS 新建一个连接或者用sqlcmd -U rpt_user -P 密码 -d BusinessDB试一下。两个层面都验证过才算真正交付。3.3 权限清单导出与交付文档权限配好之后我强烈建议导出一份权限清单存档。一方面方便审计另一方面以后排查问题时有个基准。SQL Server 2008 R2 没有现成的一键导出权限功能得自己写查询USE [BusinessDB]; GO SELECT prin.name AS PrincipalName, prin.type_desc AS PrincipalType, perm.permission_name AS Permission, perm.state_desc AS State, CASE perm.class WHEN 0 THEN DATABASE WHEN 1 THEN OBJECT WHEN 3 THEN SCHEMA END AS Scope, COALESCE(OBJ_NAME(perm.major_id), SCHEMA_NAME(perm.major_id), N/A) AS TargetName FROM sys.database_permissions perm JOIN sys.database_principals prin ON perm.grantee_principal_id prin.principal_id WHERE prin.name rpt_user ORDER BY Scope, TargetName; GO这条查询会把指定用户的所有显式权限列出来包括 GRANT 和 DENY。配合角色成员查询就能拼出完整的权限画像USE [BusinessDB]; GO SELECT r.name AS RoleName, m.name AS MemberName FROM sys.database_role_members rm JOIN sys.database_principals r ON rm.role_principal_id r.principal_id JOIN sys.database_principals m ON rm.member_principal_id m.principal_id WHERE m.name rpt_user; GO把这两份结果整理成表格存档交付给运维或者写进项目文档后续任何人接手都能快速搞清楚这个账号的权限边界。这个习惯我坚持了很多年省下的沟通成本难以估量。4. 常见问题与排查技巧实录4.1 登录失败类问题的排查路径权限配置过程中最常见的报错就是登录失败但登录失败这四个字背后可能有好几种原因得按顺序排查。第一种报错登录失败用户 xxx 登录失败错误 18456。这通常是密码错误或者登录名不存在。先确认登录名是否真的建在正确的实例上再确认密码。如果实例是仅 Windows 认证模式SQL 登录名根本用不了这个前面提过。第二种报错无法打开登录所请求的数据库 BusinessDB。这就是默认库权限问题。用户登录时默认要进DEFAULT_DATABASE指定的库但如果没有这个库的访问权限登录直接失败。解决办法要么给这个库的访问权限要么把默认库改成master。这个坑我踩过不止一次尤其是给新账号配了默认库却忘了建用户映射的时候。第三种能登录但看不到任何表。这通常是默认架构设错了或者权限授在了错误的架构上。用EXECUTE AS模拟一下看具体哪张表查不了再对照sys.database_permissions查权限落在哪个层级。下面这张速查表把常见现象和对应原因整理在一起方便快速定位现象最可能的原因快速验证方法错误 18456 登录失败密码错/登录名不存在/认证模式不符查 sys.server_principals确认认证模式无法打开默认数据库默认库无访问权限查用户是否在默认库有映射能登录但看不到表默认架构错/权限授错架构EXECUTE AS 模拟 查权限视图有角色却查不了表存在显式 DENY查 sys.database_permissions 的 DENY 记录还原数据库后连不上孤儿用户SID 不匹配查用户 SID 与登录名 SID 是否一致4.2 孤儿用户与 SID 不匹配的修复实录孤儿用户是数据库迁移场景下的高频问题。典型表现是数据库从 A 服务器还原到 B 服务器B 服务器上明明建了同名登录名但用户就是连不上或者连上了权限全无。根本原因是数据库用户里存的 SID 是 A 服务器上那个登录名的 SID而 B 服务器上同名登录名的 SID 是另一个值。名字一样不代表身份一样SQL Server 认的是 SID 不是名字。诊断方法很简单查一下两边的 SID 对比-- 查数据库用户的 SID USE [BusinessDB]; SELECT name, sid FROM sys.database_principals WHERE name rpt_user; -- 查服务器登录名的 SID USE master; SELECT name, sid FROM sys.server_principals WHERE name rpt_user;两个 SID 不一致就是孤儿用户。修复用一条语句搞定USE [BusinessDB]; GO ALTER USER [rpt_user] WITH LOGIN [rpt_user]; GO这条语句会按名字找到服务器上的登录名把数据库用户的 SID 同步过去。执行完再查一次 SID两边一致就修好了。这个操作我在每次数据库迁移后都会批量跑一遍把所有用户重新映射一次一劳永逸。注意ALTER USER ... WITH LOGIN只对 SQL 登录名和 Windows 登录名有效。如果数据库用户原本是WITHOUT LOGIN建的比如证书签名用户、应用程序角色这条语句会报错需要区别对待。4.3 权限叠加与最小权限的平衡技巧权限管理最容易走两个极端要么给得太少天天来申请要么给得太多埋下隐患。我的经验是建立一套权限模板按岗位预定义好几套标准权限组合新账号直接套模板既快又不会出错。比如可以定义这么几套模板报表查询岗db_datareader 特定架构 SELECT、业务操作岗db_datareaderdb_datawriter、数据导入岗db_datareaderdb_datawriterbulkadmin、只读审计岗db_datareader 显式 DENY 敏感表。每套模板对应一段标准脚本新需求来了先看能不能套模板套不上再单独设计。另一个技巧是善用拒绝来做减法。与其从零开始一点点加权限不如先给一个基础角色再用 DENY 把不该看的减掉。这种加法打底、减法收口的思路在权限需求频繁变动的场景下维护成本更低。但前提是目标用户不能是db_owner或sysadmin否则 DENY 无效。最后提醒一句权限配置完成后一定要做一次完整的回归验证用新账号实际连一次库、跑几条典型查询、试一下越权访问是否被正确拦截。我见过太多配置看起来没问题、一上线就出问题的案例都是因为跳过了这最后一步验证。权限这东西宁可多花十分钟验证也别等出事再回头查。
阅读完成 · 觉得有帮助？
