MSSQL 2016 如何添加只读数据库用户：GRANT SELECT 表与视图完整教程

SQL Server 2016 经常要给 BI、报表、运维或外部接口开一个专用账号，要求只读表与视图、不能改数据、不能删数据、看不到其它数据库的隐私数据。如果直接给 db_datareader、db_datareaderwriter、db_owner 这种泛角色，账号权限就可能过大，BI 误操作或被入侵后影响业务。

本文用 SQL Server Management Studio 和 T-SQL 两种方式，演示给单个业务数据库添加只读账号的完整流程：建登录、建用户、映射到目标库、用 db_datareader 数据库角色授权 SELECT 权限，并补充视图、模式、跨库访问的进阶控制。

一、前置条件：环境与版本

下面示例以 SQL Server 2016 为参考，也适用于 2017/2019/2022 等版本。执行前请确认：

• SQL Server 服务正常运行，使用 SQL Server Management Studio 或 sqlcmd 可登录
• 登录账号具有创建登录和用户的权限，至少是 securityadmin 角色成员
• 已有目标业务数据库，本例使用 YourAppDB
• 服务器身份验证模式至少为混合模式，能创建 SQL Server 登录

SELECT @@VERSION;
SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourAppDB';

查询语法和角色名各版本基本一致，但 Always Encrypted、外部表、列级权限等高级话题以你的 SQL Server 实际版本为准。

二、理解 SQL Server 登录和用户

SQL Server 有两层身份：

• 登录 Login：在服务器实例级别，登录后能看见多个数据库
• 用户 User：在单个数据库级别，把登录映射为该库内的身份

添加只读账号的思路是：先建服务器级登录，再在目标数据库里建同名用户并授予 db_datareader 角色。这样账号能 SELECT 表和视图，但不能 INSERT、UPDATE、DELETE，也不能访问其它业务库。

三、SSMS 图形化操作步骤

适合刚接手项目或需要截图给同事看的场景。

1. 用管理员账号登录 SSMS，展开 Security 节点
2. 右键 Logins → New Login，进入 Login – New 窗口
3. General：选择 SQL Server authentication，输入 Login name，例如 app_report_ro，并设置强密码，去掉 Enforce password policy 后面的勾可避免复杂策略；或选择 Windows authentication 直接用域账号
4. User Mapping：勾选 YourAppDB，在 Database role membership 中只勾 db_datareader
5. Status：勾选 Permission to connect to database engine 和 Login is enabled
6. 点击 OK 完成创建

完成后展开 YourAppDB → Security → Users，会看到 app_report_ro 出现在用户列表中。

四、T-SQL 完整脚本

生产环境推荐脚本化，便于审计和跨服务器批量执行。请把 YourAppDB 替换成实际数据库名，把强密码替换成符合策略的密码。

-- 1. 在服务器实例级别创建登录
CREATE LOGIN [app_report_ro]
WITH PASSWORD = N'请改成一个强密码!@#2026',
     DEFAULT_DATABASE = [YourAppDB],
     CHECK_POLICY = OFF,
     CHECK_EXPIRATION = OFF;
GO

-- 2. 在目标数据库里创建同名用户并映射到上述登录
USE [YourAppDB];
GO

CREATE USER [app_report_ro]
FOR LOGIN [app_report_ro]
WITH DEFAULT_SCHEMA = [dbo];
GO

-- 3. 授予 db_datareader 角色，等价于对所有用户表和视图 SELECT
ALTER ROLE [db_datareader] ADD MEMBER [app_report_ro];
GO

-- 4. 默认用户不能访问其他业务库。如需主动拒绝，启用 Deny 显式
USE [master];
GO
DENY VIEW ANY DATABASE TO [app_report_ro];
GO

脚本执行后立即生效，不需要重启 SQL Server。CHECK_POLICY 在演示里设为 OFF，生产环境应结合企业密码策略决定是否开启。

五、验证只读账号

在 SSMS 断开当前管理员会话，使用 app_report_ro 重新登录。

SELECT SUSER_NAME() AS 当前登录, USER_NAME() AS 当前用户, DB_NAME() AS 当前数据库;

-- 验证 1：能读表
SELECT TOP 5 * FROM dbo.Orders;

-- 验证 2：能读视图
SELECT TOP 5 * FROM dbo.v_OrderSummary;

-- 验证 3：写入被拒绝，应该报权限错误
INSERT INTO dbo.Orders (OrderNo, Amount) VALUES ('TEST', 100);

-- 验证 4：不能访问其他业务库
USE [OtherAppDB];
GO
SELECT TOP 1 * FROM dbo.SomeTable;

前三项的结果：1 和 2 正常返回数据，3 应报 The INSERT permission was denied on the object ‘Orders’，4 应报 The server principal ‘app_report_ro’ is not able to access the database ‘OtherAppDB’。

六、视图和架构的只读补充

有时候企业希望账号只能查视图不能看原始表，或者只允许访问指定 schema。

6.1 只读视图不读原始表

USE [YourAppDB];
GO

-- 关键视图允许 SELECT
GRANT SELECT ON [dbo].[v_OrderSummary] TO [app_report_ro];
GO

-- 原始表收回默认读取权限
REVOKE SELECT ON [dbo].[Orders] FROM [app_report_ro];
GO

6.2 只允许指定 schema

USE [YourAppDB];
GO

-- 先撤销 db_datareader 角色
ALTER ROLE [db_datareader] DROP MEMBER [app_report_ro];
GO

-- 仅授予报表 schema 的 SELECT
GRANT SELECT ON SCHEMA::[rpt] TO [app_report_ro];
GO

6.3 只读表函数、视图函数、存储过程

对于 BI 调用了表值函数或存储过程，BI 账号还需要 EXECUTE 权限：

USE [YourAppDB];
GO
GRANT EXECUTE ON [dbo].[usp_GetReportData] TO [app_report_ro];
GO

db_datareader 角色本身不包含 EXECUTE，需要按需逐个 GRANT。

七、跨数据库访问控制

默认情况下 SQL Server 登录可以枚举所有数据库。要实现 app_report_ro 只能看到 YourAppDB，看不到其他业务库：

USE [master];
GO

-- 显式拒绝枚举所有数据库
DENY VIEW ANY DATABASE TO [app_report_ro];
GO

-- 然后在允许的数据库中创建用户
USE [YourAppDB];
GO
CREATE USER [app_report_ro] FOR LOGIN [app_report_ro];
ALTER ROLE [db_datareader] ADD MEMBER [app_report_ro];
GO

-- 其他业务库不建用户，登录自然无法访问
USE [OtherAppDB];
GO
-- 不要在这里创建 app_report_ro 用户
-- 如果已经创建，先删除
DROP USER IF EXISTS [app_report_ro];
GO

另一种更严格的方案是 Contained Database，数据库自带用户定义，业务数据库在带服务器登录脱机后仍然可用，跨库访问被强制隔离。

八、列级只读、字段脱敏、行级过滤

有些场景需要更细的权限：

• 列级只读：表里有些字段是手机号、身份证号或密码，BI 账号不能看到原始值
• 行级过滤：BI 只能看到本部门的数据
• 数据脱敏：定期覆盖表里脱敏后的数据，或用动态数据脱敏 Dynamic Data Masking

SQL Server 2016 本身支持 Grant SELECT(col1, col2) ON table TO user 这种列级语法，可按字段精确放开。DDM 用 GRANT UNMASK 控制原始数据查询，结合视图和列级授权可以拼出更细的合规方案。

九、安全加固建议

1. 只读账号也不要使用简单的 read、guest、test 名称，命名按服务或用途，例如 app_report_ro、bi_user_ro
2. 定期轮换密码，并把密码写进企业密码管理器，不要写在脚本注释里
3. 结合登录触发器或审计，监控异常 SELECT 行为，例如全表扫、深夜查询
4. 同时打开 SQL Server 审计或扩展事件，记录 app_report_ro 的实际操作
5. 为 BI 报表服务器设置 IP 白名单，让账号只能从 BI 网关连接
6. CHECK_POLICY 默认开启，结合企业域密码策略
7. 使用 Always Encrypted 保护敏感列，从数据库引擎层加密业务敏感字段

十、撤销和清理只读账号

BI 项目结束、第三方对接下线时，账号必须及时回收：

-- 1. 撤销角色
USE [YourAppDB];
GO
ALTER ROLE [db_datareader] DROP MEMBER [app_report_ro];
GO

-- 2. 删除数据库用户
DROP USER IF EXISTS [app_report_ro];
GO

-- 3. 删除服务器登录
USE [master];
GO
DROP LOGIN [app_report_ro];
GO

Drop 顺序为：先角色 → 再用户 → 再登录。直接 Drop 登录不删用户会留下孤立用户记录，容易被审计忽略。

十一、生产环境部署清单

• 账号命名规范、密码策略、轮换周期都写进企业 DBA 规范
• 正式上线前在测试库跑完整脚本，验证 5 次读操作和 3 次写拦截
• BI 或报表项目归档时，把对应账号、数据库、视图、依赖关系登记到配置库
• 用 SQL Server Audit 或扩展事件记录只读账号的登录和批量查询
• 如果同一个 BI 平台对接多个业务库，每个业务库单独建一个只读账号，便于收回
• 当员工离职或 BI 厂商合作结束，立即按本文第十节顺序回收账号

十二、最终总结

在 SQL Server 2016 中添加只读账号的最小可用版本其实只有三句：CREATE LOGIN、CREATE USER、ALTER ROLE db_datareader ADD MEMBER。剩下的是围绕它做权限收紧、视图限制、跨库隔离、列级控制和安全加固。

推荐使用 db_datareader 角色而不是直接 GRANT SELECT ON :: ALL TABLES，前者更标准，数据库结构变化时也无需重写权限脚本。当业务对权限要求更细时，再按视图、Schema、列、DDM 等维度补充。

官方参考：SQL Server 权限（数据库引擎）、主体（数据库引擎）、数据库级别角色。以 SQL Server 当前版本和官方帮助文档为准。