SQL Server 设置只读账号 ReadOnlyUser

一、创建只读账号完整脚本

-------------------------------------------------------------------------------
-- 1. 创建服务器登录名（只读用户）
-------------------------------------------------------------------------------
CREATE LOGIN ReadOnlyUser
WITH PASSWORD = '123456';    --只读用户密码

-------------------------------------------------------------------------------
-- 2. 在目标数据库中创建用户（映射到登录名）
-------------------------------------------------------------------------------
USE Mydatabasecs;
CREATE USER ReadOnlyUser FOR LOGIN ReadOnlyUser;

-------------------------------------------------------------------------------
-- 3. 授予只读权限（加入 db_datareader 角色）
-- 该角色拥有数据库中所有表的 SELECT 权限
-------------------------------------------------------------------------------
ALTER ROLE db_datareader ADD MEMBER ReadOnlyUser;

-------------------------------------------------------------------------------
-- 4. 显式拒绝写入权限（双重保险）
-- 确保即使有其他权限继承，也无法写入
-------------------------------------------------------------------------------
DENY INSERT, UPDATE, DELETE, EXECUTE TO ReadOnlyUser;

-------------------------------------------------------------------------------
-- 5. 更改数据库所有者为 sa（关键安全步骤）
-- 避免 ReadOnlyUser 因继承所有者权限而获得写入能力
-------------------------------------------------------------------------------
USE Mydatabasecs;
EXEC sp_changedbowner 'sa';

 

二、过程中遇到的问题及解决方法

问题 1：登录失败，错误 18456
现象：用 ReadOnlyUser 登录时提示"用户'ReadOnlyUser'登录失败"
原因：SQL Server 身份验证模式为"仅 Windows 身份验证"
解决：修改为"SQL Server 和 Windows 身份验证模式"并重启 SQL Server 服务

问题 2：只读账号能成功执行 INSERT 写入
现象：ReadOnlyUser 执行 INSERT 语句成功写入数据
原因：ReadOnlyUser 映射的 Windows 用户是数据库所有者，拥有完整权限
解决：通过 EXEC sp_changedbowner 'sa' 将所有者权限移交给 sa，再用 DENY 显式拒绝写入

三、成功后效果验证

查询权限（成功）：
SELECT TOP 10 * FROM  dbo.Test_Class;

执行结果：
(10 行受影响)

写入权限（失败）：
INSERT INTO dbo.Test_Class (ClassID, ClassName) VALUES (NEWID(), 'test');

执行结果：
消息 229，级别 14，状态 5，第 5 行
拒绝了对对象 'Test_Class'（数据库 'Mydatabasecs'，架构 'dbo'）的 INSERT 权限。

完成时间: 2026-08-15T15:06:09.2895195+08:00

四、最终结论

- ✅ SELECT 查询正常返回数据
- ❌ INSERT、UPDATE、DELETE 均被拒绝
- ✅ 账号已成功限制为**只读权限**，安全可投入使用

五、其他

ReadOnlyUser 是用户账号名，是可以自定义的，不是固定值。