1. 为什么只读账号这件事值得单独拿出来讲给数据库开只读账号听起来像是DBA入门第一天的活儿但我见过太多团队在这件事上翻车。有人图省事直接把账号塞进sysadmin角色有人只给了db_datareader却发现对方连一张视图都查不了还有人开完账号忘了限制连接来源最后报表系统把生产库的连接池占满。这些问题的根源都指向同一个事实只读在 SQL Server 里从来不是一个开关而是一组权限的组合决策。这篇内容面向的是需要给外部系统、BI 工具、报表平台、审计人员或者临时协作方开放数据库查询权限的开发和运维同学。不管你习惯用 SSMS 图形界面点鼠标还是更信任 T-SQL 脚本可复现、可版本化的方式下面两种场景我都会完整走一遍。核心关键词围绕SQL Server、只读账号、SSMS、T-SQL、db_datareader展开同时会把权限边界、常见坑点和验证方法讲透。先说一个容易被忽略的前提SQL Server 的权限体系是分层的服务器级别有登录名Login数据库级别有用户User再往下才是角色和具体对象权限。只读账号的创建本质上要打通这三层任何一层缺失都会导致账号建了但连不上或者连上了但查不了。理解了这条主线后面所有操作你都能自己推导出来。2. 动手之前先想清楚只读账号的权限边界怎么划2.1 服务器层与数据库层是两套东西很多人第一次建账号会懵为什么我在安全性-登录名里建好了账号对方连上来却报无法打开数据库原因就在于登录名只解决了能不能进这栋楼的问题而能不能进某个房间要靠数据库用户来映射。SQL Server 里这两者是分开的对象登录名存在于服务器实例级别用户存在于具体的数据库内部两者通过 SID 关联。所以完整的只读账号创建流程一定是两步先建服务器级登录名再在目标数据库里建用户并绑定这个登录名最后给用户授予读取权限。SSMS 的图形界面把这两步拆在了不同节点下T-SQL 则用CREATE LOGIN和CREATE USER两条语句分别完成。2.2 db_datareader 到底给了什么没给什么db_datareader是数据库固定角色成员可以对该数据库中所有用户表执行SELECT。注意几个关键限定它只覆盖用户表系统表、部分系统视图不在自动授权范围内它给的是SELECT不含EXECUTE所以存储过程默认执行不了它不包含对视图的显式授权——这点争议很大实际上在多数版本中db_datareader成员能查视图但前提是视图的所有者与查询者权限链能打通遇到跨库视图或所有权链断裂就会失败它不涉及任何写操作INSERT/UPDATE/DELETE一律没有。如果你的只读需求只是让报表工具能查业务表db_datareader基本够用。但如果对方要调用存储过程、要查跨库视图、要访问特定 schema就得在这个角色之外单独补授权。我个人的习惯是能用角色解决就不逐个对象授权但角色覆盖不到的地方一定要显式补上并写进交接文档否则半年后没人记得这个账号为什么查不了某张表。2.3 两种创建方式的取舍SSMS 图形界面适合一次性操作、给不熟悉脚本的同事演示、或者临时开个账号。它的优势是直观每一步都有界面提示不容易漏掉映射用户这种关键步骤。缺点是难以复现换个环境你得重新点一遍而且操作记录不留痕。T-SQL 脚本适合需要批量创建、需要纳入版本管理、需要在多个环境开发/测试/生产保持一致性的场景。一条CREATE LOGIN加一条CREATE USER加一条ALTER ROLE三行搞定还能存进 Git。缺点是对新手不够友好密码策略、默认数据库、默认语言这些参数如果没写全可能建出来的账号行为和预期不一致。我的建议是生产环境一律用脚本图形界面只用来做验证和排查。下面两种场景我都会给出完整操作你可以按自己的习惯选。3. 场景一SSMS 图形界面完整操作链路3.1 创建服务器级登录名打开 SSMS 连接到目标实例在对象资源管理器里展开安全性节点右键登录名选择新建登录名。弹出的窗口里几个关键填写项登录名建议用有明确用途的命名比如ro_report、readonly_bi别用test、user1这种。命名规范能帮你在半年后一眼看出这个账号是干嘛的。身份验证选SQL Server 身份验证勾选强制实施密码策略和强制密码过期看你的实际需求。如果是给程序用的服务账号通常取消强制密码过期否则密码到期后程序会突然连不上排查起来很折腾。默认数据库一定要改成目标业务库不要留master。默认库是master的账号登录后会直接进系统库既容易误操作也会让一些连接池配置出现意外行为。默认语言保持默认即可除非有特殊排序需求。切到用户映射页这是最容易漏的一步。勾选目标数据库在下方的数据库角色成员身份里勾上db_datareader。如果你还想让它能执行存储过程可以同时勾db_executor注意这个角色不是系统自带的需要先手动创建后面会讲。点确定后登录名和数据库用户会一次性建好这是图形界面相对脚本的一个便利之处——它把两步合并了。3.2 验证账号是否真的只能读建完账号别急着交付一定要自己先验证一遍。用新账号在 SSMS 里重新开一个连接然后依次执行-- 应该成功 SELECT TOP 10 * FROM 你的业务表; -- 应该失败报权限不足 INSERT INTO 你的业务表 (某字段) VALUES (test); UPDATE 你的业务表 SET 某字段 test WHERE 主键 1; DELETE FROM 你的业务表 WHERE 主键 1; -- 应该失败 DROP TABLE 某张测试表;如果SELECT成功而写操作全部报错说明权限边界正确。如果SELECT也失败回到用户映射检查角色是否勾选成功或者确认你查的表是否在db_datareader的覆盖范围内。提示验证时不要用sa或者自己的管理员账号测一定要用新建的只读账号重新登录。权限问题只有站在目标账号的视角才能暴露出来。3.3 图形界面里那些容易点错的地方有几个坑我在实际带人时反复见到第一登录名窗口的状态页里有个登录选项默认是启用。如果你不小心点成禁用账号建了也连不上而且报错信息不会直接告诉你账号被禁用了只会提示登录失败很容易往密码方向排查。第二用户映射页勾选数据库后如果不勾任何角色用户会被建出来但没有任何权限表现为能连上库但什么都查不了。这种情况比连不上更隐蔽因为连接是成功的。第三有些版本的 SSMS 在用户映射页勾选db_datareader后如果目标库里存在同名的 schema 或者特殊的所有权链实际权限可能和预期有偏差。遇到查不了的情况先用管理员账号执行EXECUTE AS USER 你的只读用户再查一次能快速定位是权限问题还是对象本身的问题。4. 场景二T-SQL 脚本方式可复现可版本化4.1 三段式脚本模板脚本方式我习惯拆成三段建登录名、建用户、授角色。分开写的好处是每一步都可以单独重跑排查问题时能精确定位到哪一步失败。-- 第一段创建服务器级登录名 USE master; GO CREATE LOGIN ro_report WITH PASSWORD YourStrongPassword123!, DEFAULT_DATABASE [YourBusinessDB], CHECK_POLICY ON, CHECK_EXPIRATION OFF; GO这里CHECK_POLICY ON会强制密码符合 Windows 密码复杂度要求生产环境建议开启。CHECK_EXPIRATION OFF表示密码不过期适合服务账号如果是给人用的临时账号可以设为ON。-- 第二段在目标数据库中创建用户并映射登录名 USE [YourBusinessDB]; GO CREATE USER ro_report FOR LOGIN ro_report; GO-- 第三段授予只读角色 ALTER ROLE db_datareader ADD MEMBER ro_report; GO三段跑完账号就能用了。如果你需要它在多个库都有只读权限对每个库重复第二、三段即可登录名只需要建一次。4.2 密码策略与默认库的参数取舍CREATE LOGIN里几个参数值得单独说参数作用建议值CHECK_POLICY是否套用系统密码复杂度策略生产环境 ONCHECK_EXPIRATION密码是否过期服务账号 OFF人工账号 ONDEFAULT_DATABASE登录后的默认库指向业务库别留 masterDEFAULT_LANGUAGE默认语言一般留默认DEFAULT_DATABASE这个参数特别容易被忽略。如果留空账号登录后落在master某些 ORM 框架或者连接池在初始化时会执行一些元数据查询落在master可能导致行为异常。我遇到过报表工具因为默认库是master而反复报对象不存在排查了半天才发现是默认库的问题。4.3 脚本方式的幂等处理生产环境跑脚本最怕的是重复执行报错。CREATE LOGIN和CREATE USER如果对象已存在都会直接报错中断。稳妥的写法是先判断再创建USE master; GO IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name ro_report) BEGIN CREATE LOGIN ro_report WITH PASSWORD YourStrongPassword123!, DEFAULT_DATABASE [YourBusinessDB], CHECK_POLICY ON, CHECK_EXPIRATION OFF; END GO USE [YourBusinessDB]; GO IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name ro_report) BEGIN CREATE USER ro_report FOR LOGIN ro_report; END GO ALTER ROLE db_datareader ADD MEMBER ro_report; GOALTER ROLE ... ADD MEMBER本身是幂等的重复执行不会报错所以不需要额外判断。这套脚本可以直接放进你的数据库初始化脚本集里新环境部署时一把跑完。5. db_datareader 覆盖不到的场景怎么补5.1 存储过程执行权限db_datareader不含EXECUTE。如果只读账号需要调用存储过程有两种做法一是给单个存储过程授权GRANT EXECUTE ON 存储过程名 TO ro_report二是创建一个自定义的db_executor角色统一管理。-- 创建自定义执行角色 USE [YourBusinessDB]; GO CREATE ROLE db_executor; GO GRANT EXECUTE TO db_executor; GO -- 把只读账号加入 ALTER ROLE db_executor ADD MEMBER ro_report; GO注意GRANT EXECUTE TO db_executor是在数据库级别授予执行权限意味着这个角色能执行库里所有存储过程。如果你的库里有敏感的写操作存储过程这样做等于变相开了写权限要谨慎。更精细的做法是逐个存储过程授权。5.2 视图与跨库查询视图的权限问题比较绕。如果视图和基表在同一个库、同一个所有者下db_datareader成员通常能正常查询因为所有权链会打通。但如果视图引用了其他库的表或者视图的所有者和基表所有者不一致所有权链断裂查询就会报权限不足。遇到这种情况最直接的办法是给视图显式授权GRANT SELECT ON [dbo].[你的视图名] TO ro_report;跨库查询则需要在每个涉及的库里都建用户并授权或者考虑用同义词Synonym加存储过程封装的方式收敛权限。这块展开能写一整篇这里先记住原则所有权链能打通就不用额外授权打不通就显式补别指望角色能覆盖一切。5.3 限制连接来源与资源占用只读账号虽然不能写数据但能占资源。一个失控的报表查询可以把 CPU 打满或者把连接池耗尽。几个实用的限制手段用登录名的状态页限制同时连接数图形界面或通过资源调控器Resource Governor限制 CPU 和内存给账号设置独立的连接超时和查询超时在应用侧配置如果是 SQL Server 2016 及以上可以用资源调控器创建独立的资源池把只读账号绑定进去。资源调控器的配置稍微复杂简单场景下至少做到限制最大连接数和应用侧设置查询超时能挡掉大部分意外。6. 排查实录账号建好了却连不上或查不了6.1 登录失败的三类原因账号建完连不上按这个顺序排查第一确认登录名是否启用。查sys.server_principals的is_disabled字段SELECT name, is_disabled FROM sys.server_principals WHERE name ro_report;返回 1 就是被禁用了用ALTER LOGIN ro_report ENABLE;启用。第二确认身份验证模式。如果实例只允许 Windows 身份验证SQL 登录名根本用不了。查服务器属性安全性页或者SELECT SERVERPROPERTY(IsIntegratedSecurityOnly);返回 1 表示仅 Windows 验证需要改成混合模式并重启服务。第三确认密码和策略。如果CHECK_POLICY ON而密码不符合复杂度创建时就会失败。如果账号被锁定多次输错密码也会登录失败需要ALTER LOGIN ro_report WITH PASSWORD 新密码 UNLOCK;解锁。6.2 能连上但查不了表这种情况通常是用户映射或角色没生效。先确认用户是否存在并绑定了正确的登录名USE [YourBusinessDB]; SELECT dp.name AS 用户名, sp.name AS 登录名, dp.type_desc FROM sys.database_principals dp LEFT JOIN sys.server_principals sp ON dp.sid sp.sid WHERE dp.name ro_report;如果登录名显示为 NULL说明用户的 SID 和登录名对不上常见于数据库是从别的实例还原过来的。解决办法是用ALTER USER ro_report WITH LOGIN ro_report;重新绑定。再确认角色成员身份SELECT r.name AS 角色名 FROM sys.database_role_members rm JOIN sys.database_principals r ON rm.role_principal_id r.principal_id JOIN sys.database_principals u ON rm.member_principal_id u.principal_id WHERE u.name ro_report;应该能看到db_datareader。如果没有重新执行ALTER ROLE db_datareader ADD MEMBER ro_report;。6.3 用 EXECUTE AS 快速定位权限问题排查权限问题时EXECUTE AS USER是神器。用管理员账号执行USE [YourBusinessDB]; EXECUTE AS USER ro_report; SELECT TOP 1 * FROM 你的业务表; REVERT;如果这里能查而实际连接查不了问题在连接层默认库、连接字符串等如果这里也查不了问题在权限层。这个技巧能帮你把连接问题和权限问题快速分开省下大量猜测时间。7. 几个我踩过的坑和长期维护建议第一个坑是默认库设成 master 导致程序报错。前面提过这里再强调一次尤其是用 ORM 框架的项目初始化时的一些查询会依赖默认库落在 master 上行为完全不对。第二个坑是账号密码硬编码在连接字符串里然后进了代码仓库。只读账号虽然权限有限但泄露了照样能被拖库。建议用配置中心或者密钥管理服务至少也要用环境变量。第三个坑是账号建完就没人管了。离职人员、下线系统对应的只读账号如果不清时间长了就是一堆僵尸账号。我的做法是给每个只读账号在命名里带上用途和创建日期比如ro_bi_202401然后每季度用脚本扫一遍sys.server_principals对照台账清理。第四个坑是以为只读就绝对安全。只读账号能查所有业务表如果表里有敏感信息手机号、身份证、薪资等于把这些数据开放给了所有能拿到这个账号的人。敏感字段该脱敏脱敏该用视图封装就封装别把只读等同于可以随便看。长期维护上我建议把只读账号的创建脚本纳入数据库版本管理和建表脚本放在一起。新环境部署时自动执行避免手工操作遗漏。同时定期审计db_datareader的成员列表确认每个成员都还有存在的必要。这套流程跑顺了只读账号这件事就再也不会成为半夜被叫起来排查的故障源。