1. 为什么“只读账号”这件事值得单独拎出来讲但凡在生产环境摸过几年数据库的人大概都经历过这样的场景业务方跑来找你说想连一下数据库查点数据做报表也好排查问题也好反正就是“只看不改”。这时候如果你图省事直接把 sa 或者某个高权限账号丢过去那基本等于把家门钥匙连同保险柜密码一起交出去了。轻则误操作改了几行数据重则一个DELETE忘了加WHERE整张表清空半夜被电话叫起来恢复备份的滋味可不好受。所以给数据库创建一个只读账号是 DBA 日常里最基础、也最容易被忽视的一项安全操作。它的核心诉求非常明确这个账号只能SELECT不能INSERT、UPDATE、DELETE更不能改表结构、删库、加账号。听起来简单但真要做到“干净利落的只读”里面有不少细节值得说道。这篇内容我打算把 SQL Server 创建只读账号这件事彻底讲透覆盖两种最常见的操作路径一种是SSMS 图形界面点点点完成适合不熟悉脚本、或者临时应急的场景另一种是T-SQL 脚本适合批量、可复现、可纳入版本管理的场景。两种方式我都会给出完整步骤并且把每一步背后的逻辑讲清楚让你不只是“照着做”而是明白“为什么这么做”。适合谁看刚入行的运维、后端开发、数据分析师以及任何需要给他人开数据库查询权限但又不想给太高权限的人。哪怕你之前没怎么碰过 SQL Server 的权限体系跟着走一遍也能上手。在开始之前先明确一个概念SQL Server 的权限控制是分层的。服务器级别有“登录名”Login数据库级别有“用户”User然后才是角色和具体权限。很多人第一次配只读账号会卡住就是因为把“登录名”和“用户”搞混了——登录名负责“能不能连上服务器”用户负责“连上之后在某个数据库里能干什么”。这两步缺一不可后面我会反复强调。2. 动手前的准备把概念和前提理清楚2.1 登录名、用户、角色三者到底啥关系我用一个生活化的类比来解释。把 SQL Server 实例想象成一栋写字楼登录名就是你在写字楼大门口刷的门禁卡有了它你才能进楼。进了楼之后你要去某个具体的公司数据库办事那家公司得给你发一张工牌这张工牌就是用户。而角色呢相当于公司里预设好的岗位权限包比如“访客”这个岗位默认只能看不能动你把工牌挂到“访客”这个岗位上就自动获得了对应的权限。所以创建只读账号的完整链路是先建登录名门禁卡→ 再在目标数据库里建用户工牌→ 把用户加入db_datareader角色挂到访客岗位。三步走完一个标准的只读账号才算成型。这里有个常见误区有人只建了登录名然后发现用这个账号连上数据库后啥也看不到就以为权限没生效。其实是因为没有在数据库里建对应的用户登录名进得来楼但进不了任何一家公司的门。2.2 版本差异与前提条件这套操作在 SQL Server 2008 到 2022 之间基本通用db_datareader这个固定数据库角色从很早的版本就存在了所以不用担心版本兼容问题。不过有两点要注意第一你需要有一个具备足够权限的账号来执行创建操作。通常用sa或者属于sysadmin固定服务器角色的账号或者至少是对目标数据库有db_owner权限的账号。权限不够的话建登录名这一步就会报错。第二如果你用的是 Azure SQL Database 这类云上版本登录名的创建语法略有不同它不支持实例级登录名而是用包含数据库用户但db_datareader角色的用法是一样的。本文以本地部署的 SQL Server 为主云版本的差异我会在注意事项里点一下。提示动手之前先确认你连的是哪个实例、哪个数据库。生产环境操作前强烈建议先在测试库上走一遍流程确认无误再上生产。2.3 只读账号的权限边界先想清楚再动手在动手之前我建议你先花两分钟想清楚这个账号到底需要读哪些东西是只需要读某几张表还是整个库都要能读是只读表还是视图、存储过程也要能执行db_datareader这个角色给的是整个数据库所有用户表的 SELECT 权限粒度比较粗。如果你的需求是“只能看某几张表”那用角色就不合适了得用更细的GRANT SELECT ON 表名 TO 用户名逐表授权。反过来如果业务方还需要执行某些存储过程来取数那光有db_datareader还不够因为存储过程的执行权限是独立的需要额外GRANT EXECUTE。我个人的经验是能用角色解决的就别逐表授权因为逐表授权在表结构变更、新增表的时候很容易漏维护成本高。除非有明确的合规要求必须最小权限否则db_datareader是性价比最高的选择。这个判断逻辑后面在两种操作方式里都会体现。3. 场景一SSMS 图形界面创建只读账号3.1 打开 SSMS 并连接到目标实例先打开 SQL Server Management StudioSSMS。如果你还没装去微软官网下载最新版即可SSMS 是免费工具版本迭代比较快用较新的版本连接老版本 SQL Server 一般没问题。连接的时候服务器名称填实例地址身份验证方式选“SQL Server 身份验证”或“Windows 身份验证”都行取决于你手上有什么高权限账号。连上之后在左侧“对象资源管理器”里展开到你目标的那台实例。这里要特别注意登录名是在“安全性”文件夹下创建的而用户是在具体数据库的“安全性”文件夹下创建的两个“安全性”不是同一个地方新手很容易点错。3.2 第一步创建服务器级登录名在对象资源管理器里展开“安全性”节点右键“登录名”选择“新建登录名”。弹出的窗口里几个关键填写项登录名填你想要的账号名比如readonly_user。如果选“SQL Server 身份验证”下面要设置密码并确认密码。强制实施密码策略生产环境建议勾上它会强制密码复杂度、过期策略等。测试环境嫌麻烦可以不勾但生产别省这一步。默认数据库建议设成目标业务库这样这个账号一连上来就默认进那个库省得每次手动切。填完之后先别急着点确定切到左侧的“用户映射”页。这一页是很多人会忽略的关键步骤——它让你在创建登录名的同时顺便把数据库用户也建了。在“映射到此登录名的用户”列表里勾选你的目标数据库然后在下面的“数据库角色成员身份”里勾选db_datareader。这样一步到位登录名和用户一起建好还顺手挂上了只读角色。如果你只想先建登录名用户后面单独建那“用户映射”页可以先不勾点确定完成登录名创建。但既然能一步做完何必分两步呢我一般推荐直接在“用户映射”里搞定。3.3 第二步验证用户和角色是否挂对创建完成后回到对象资源管理器展开目标数据库 → 安全性 → 用户应该能看到刚才那个用户名。右键它 → 属性 → “成员身份”页确认db_datareader已经勾上。如果没勾上手动勾一下保存即可。再展开服务器级“安全性” → “登录名”确认登录名也在。到这里图形界面的操作就算完成了。整个过程熟练的话一两分钟就能搞定非常适合临时给同事开权限的场景。3.4 图形界面的几个坑我踩过你也可能踩第一个坑在“用户映射”里勾了数据库但忘了勾角色。结果用户建出来了但没有任何权限连上后查表报“拒绝访问”。解决办法就是回到用户属性里补勾db_datareader。第二个坑默认数据库设错。如果默认数据库设成了一个该账号没权限的库账号一连上来就报错体验很差。所以默认数据库一定要设成有权限的那个。第三个坑密码策略导致登录失败。如果勾了“强制实施密码策略”而你设的密码太简单创建时可能不报错但登录时会被拒绝。这种情况排查起来比较绕建议创建时就设一个符合复杂度要求的密码。注意图形界面操作虽然直观但不可复现、不可版本化。如果你需要在多套环境开发、测试、生产重复创建同样的账号强烈建议用下一节的 T-SQL 脚本方式。4. 场景二T-SQL 脚本创建只读账号4.1 为什么脚本方式更值得掌握图形界面适合一次性、临时性的操作但真实工作里我们经常需要在多套环境重复同样的动作或者把权限配置纳入部署流程。这时候脚本的优势就出来了可复制、可审查、可版本管理、可批量执行。而且脚本能精确控制每一步出问题也容易定位。下面这套脚本我用了很多年基本覆盖了本地部署 SQL Server 创建只读账号的所有场景。我会把每一步拆开讲你可以按需组合。4.2 完整脚本与逐行解读先看完整脚本然后我逐段解释-- 1. 在 master 库创建服务器级登录名 USE master; GO CREATE LOGIN readonly_user WITH PASSWORD YourStrongPassword123!, DEFAULT_DATABASE YourTargetDB, CHECK_POLICY ON; GO -- 2. 在目标数据库创建用户并映射到登录名 USE YourTargetDB; GO CREATE USER readonly_user FOR LOGIN readonly_user; GO -- 3. 将用户加入 db_datareader 固定数据库角色 ALTER ROLE db_datareader ADD MEMBER readonly_user; GO第一段USE master是因为登录名属于服务器级别对象必须在这个上下文里创建。CREATE LOGIN里的CHECK_POLICY ON对应图形界面里的“强制实施密码策略”生产环境建议开启。DEFAULT_DATABASE就是默认数据库。第二段切到目标数据库用CREATE USER ... FOR LOGIN把登录名和用户关联起来。注意这里的用户名和登录名可以不一样但为了好维护我一般保持同名。第三段ALTER ROLE db_datareader ADD MEMBER把用户加进只读角色。老版本 SQL Server2008 之前用的是sp_addrolemember存储过程新版本推荐用ALTER ROLE语法更规范。4.3 如果只想授权部分表脚本怎么改前面说过db_datareader是整库只读。如果你确实需要更细的粒度可以不用角色改成逐表授权USE YourTargetDB; GO -- 先建用户不加入任何角色 CREATE USER readonly_user FOR LOGIN readonly_user; GO -- 逐表授予 SELECT 权限 GRANT SELECT ON dbo.Orders TO readonly_user; GRANT SELECT ON dbo.Customers TO readonly_user; GRANT SELECT ON dbo.Products TO readonly_user; GO这种方式的维护成本在于每次新增表都要补一条GRANT。所以我的建议是如果表数量少且稳定可以用如果表多且经常变还是用db_datareader省心。4.4 脚本执行后的验证方法脚本跑完不代表就万事大吉了一定要验证。最直接的办法是用新账号登录一次然后执行几条查询试试-- 用 readonly_user 登录后执行 SELECT TOP 10 * FROM dbo.SomeTable; -- 应该成功 INSERT INTO dbo.SomeTable (...) VALUES (...); -- 应该报权限错误如果SELECT成功、INSERT报错说明只读权限配置正确。如果SELECT也报错那就要检查用户是否真的加入了db_datareader或者表是否在别的 schema 下比如dbo之外的 schema角色权限是覆盖所有 schema 的但逐表授权时要写对 schema 名。另外可以用系统视图查一下权限归属USE YourTargetDB; GO SELECT dp.name AS principal_name, dp.type_desc AS principal_type, r.name AS role_name FROM sys.database_role_members drm JOIN sys.database_principals dp ON drm.member_principal_id dp.principal_id JOIN sys.database_principals r ON drm.role_principal_id r.principal_id WHERE dp.name readonly_user;这条查询能直接告诉你readonly_user属于哪些角色一目了然。5. 两种方式怎么选以及权限管理的进阶思路5.1 图形界面 vs 脚本一张表说清楚对比维度SSMS 图形界面T-SQL 脚本上手难度低点选即可中需要懂基本语法可复现性差每次都要手动点好脚本可重复执行批量部署不适合非常适合版本管理无法纳入可纳入 Git 等出错概率容易漏勾角色一次写对就稳定适用场景临时开权限、学习理解生产部署、多环境同步我的实际做法是学习阶段用图形界面建立直观认知生产环境一律用脚本。图形界面帮你理解“登录名-用户-角色”这条链路脚本帮你把这条链路固化下来。5.2 只读账号的进阶玩法行级安全和架构隔离如果你的只读需求更复杂比如“同一个账号A 部门只能看 A 部门的数据”那db_datareader就不够了需要用到行级安全Row-Level Security, RLS。RLS 通过安全策略和谓词函数让不同用户在查询同一张表时只能看到符合条件的数据行。这个配置相对复杂属于进阶话题但思路是先给用户db_datareader再用 RLS 限制可见行。另一种思路是架构隔离把敏感表放在单独的 schema 下只读账号只授予非敏感 schema 的读取权限。这样比逐表授权好维护一些因为授权单位变成了 schema。5.3 账号生命周期管理别忘了清理创建账号只是开始账号的回收同样重要。人员离职、项目结束、权限调整都需要及时清理。我见过太多环境里躺着一堆没人用的只读账号这本身就是安全隐患。清理脚本也很简单-- 从角色移除 USE YourTargetDB; GO ALTER ROLE db_datareader DROP MEMBER readonly_user; GO -- 删除数据库用户 DROP USER readonly_user; GO -- 删除服务器登录名 USE master; GO DROP LOGIN readonly_user; GO顺序很重要先移除角色成员再删用户最后删登录名。反过来删会报错因为存在依赖关系。6. 常见问题与排查技巧实录6.1 账号连不上报“登录失败”这是最高频的问题。排查顺序建议这样走先确认登录名是否真的创建成功查sys.server_principals再确认密码是否正确、密码策略是否导致登录被拒最后确认这个实例是否允许 SQL Server 身份验证有些实例只开了 Windows 身份验证模式SQL 账号根本连不上。SELECT name, type_desc, is_disabled FROM sys.server_principals WHERE name readonly_user;如果is_disabled是 1说明账号被禁用了用ALTER LOGIN readonly_user ENABLE;启用即可。6.2 能连上但查表报“拒绝访问”这种情况基本就是数据库用户或角色没配好。先确认目标库里有没有这个用户USE YourTargetDB; GO SELECT name, type_desc FROM sys.database_principals WHERE name readonly_user;如果没有说明CREATE USER这步漏了。如果有再查角色成员关系用 4.4 节那条查询。十有八九是db_datareader没挂上。6.3 常见问题速查表现象可能原因解决方向登录失败登录名未创建/密码错/账号禁用查sys.server_principals启用或重置密码登录失败实例仅 Windows 身份验证改实例认证模式或改用 Windows 账号查表拒绝访问数据库用户未创建执行CREATE USER ... FOR LOGIN查表拒绝访问未加入db_datareaderALTER ROLE db_datareader ADD MEMBER部分表查不了表在特殊 schema 或逐表授权漏了检查 schema补GRANT SELECT存储过程执行不了只读角色不含 EXECUTE 权限额外GRANT EXECUTE6.4 几个我踩过的坑分享给你第一个坑在错误的数据库上下文里建用户。有次我USE切错了库结果用户建到了测试库生产库怎么都连不上排查了半天才发现。所以脚本里USE语句一定要写对执行前多看一眼当前库。第二个坑登录名和用户名不一致导致混乱。虽然技术上允许但维护起来很痛苦尤其是排查问题时容易搞混。我现在一律保持同名。第三个坑忘了考虑连接池和缓存。有时候权限改了但现有连接还缓存着旧权限导致行为不一致。遇到这种情况让用户断开重连或者用DBCC FREEPROCCACHE清一下生产慎用。第四个坑密码策略和密码过期的组合拳。勾了密码策略后密码可能有过期时间到期后账号突然连不上业务方一脸懵。如果业务需要长期稳定的只读账号要么关掉过期策略要么建立密码轮换机制。7. 写在最后的一点个人体会权限这件事永远是“配的时候嫌麻烦出事的时候嫌配得少”。只读账号看着简单但真要做到既安全又好用需要在粒度、可维护性、业务需求之间找平衡。我个人的原则是默认给角色权限特殊需求才逐表授权生产环境一律脚本化账号创建和回收都要有记录。另外提一句如果你管理的实例比较多可以考虑把创建只读账号的脚本做成模板参数化数据库名和账号名这样一套脚本能覆盖所有实例效率提升非常明显。这个思路后续还可以扩展到其他类型的权限账号比如只写账号、报表账号等形成一套完整的权限管理规范。
