在实际的数据库运维工作中，经常会遇到这样一个需求：需要创建一个只读账号，用于给数据分析师、报表系统或者外部审计人员使用。这个账号应该能够查询所有业务库的数据，但不能做任何修改操作。如果数据库数量少，手动操作还行；但一旦库多了，几十个甚至上百个，逐个库去创建用户、分配权限，不仅效率低，还容易遗漏。

下面这套方案，就是解决这个问题的——通过一个脚本，自动遍历所有业务库，批量创建只读用户并分配权限。我们先从最基本的逻辑说起。

第一部分：创建服务器登录账号（实例级登录，用来登录整个SQL服务）

-- 创建登录账号：ReadOnly_User，这是连接数据库服务的登录凭证
CREATE LOGIN ReadOnly_User
-- 设置登录密码，N代表Unicode，避免中文密码乱码
WITH PASSWORD = N'Read@123456',
-- CHECK_EXPIRATION = OFF：关闭密码过期策略，不用定期改密码
CHECK_EXPIRATION = OFF,
-- CHECK_POLICY = OFF：关闭本地密码复杂度校验（测试/内网环境用，公网建议开启ON）
CHECK_POLICY = OFF;
-- 批处理分隔符，把上面一段单独执行完
GO

简单来说，CREATE LOGIN 创建的是服务器登录，相当于一把大门钥匙，有了它才能连上SQL Server服务。但此时，这个账号还进不了任何具体的数据库——必须在每个库里单独创建「库用户」来绑定这个登录身份。

第二部分：批量给所有业务库分配只读权限（核心循环脚本，全注释）

-- 定义变量 @dbName，用来临时存遍历到的数据库名称，SYSNAME是数据库名专用字符串类型
DECLARE @dbName SYSNAME
-- 定义变量 @sql，用来拼接动态执行的SQL语句，NVARCHAR(MAX)支持超长SQL文本
DECLARE @sql NVARCHAR(MAX)
-- 声明游标：游标是用来循环遍历多行数据的工具
DECLARE db_cursor CURSOR FOR
-- 查询系统视图sys.databases，获取当前实例所有数据库名称
SELECT name
FROM sys.databases
-- 过滤掉4个系统内置库，不给系统库分配只读权限
WHERE name NOT IN ('master','tempdb','model','msdb')
-- state = 0：只筛选「在线可用」的数据库，跳过损坏/离线库
AND state = 0
-- 打开游标，准备开始循环读取数据库列表
OPEN db_cursor
-- 读取游标第一条数据，存入变量@dbName
FETCH NEXT FROM db_cursor INTO @dbName
-- @@FETCH_STATUS 是系统内置函数：0=读取到数据，-1=读完所有数据退出循环
WHILE @@FETCH_STATUS = 0
BEGIN
    -- 拼接动态SQL字符串，针对当前循环到的数据库执行权限配置
    SET @sql = N'
        -- 切换到当前遍历到的数据库，[]包裹库名防止库名带空格/特殊字符
        USE [' + @dbName + '];
        -- 判断当前库里是否已经存在同名用户，不存在才创建，避免重复报错
        IF NOT EXISTS(SELECT 1 FROM sys.database_principals WHERE name = N''ReadOnly_User'')
            -- 在当前数据库内创建库用户，关联最开头创建的服务器登录ReadOnly_User
            CREATE USER ReadOnly_User FOR LOGIN ReadOnly_User;
        -- 判断该用户是否已经加入只读角色db_datareader，没加才执行授权
        IF NOT EXISTS(
            SELECT 1
            -- 角色成员关系表：记录哪个用户属于哪个角色
            FROM sys.database_role_members m
            -- 关联角色表，获取角色名称
            JOIN sys.database_principals r ON m.role_principal_id = r.principal_id
            -- 关联用户表，获取库内用户名称
            JOIN sys.database_principals u ON m.member_principal_id = u.principal_id
            -- 筛选：角色是只读角色db_datareader，用户是我们的只读账号
            WHERE r.name = N''db_datareader'' AND u.name = N''ReadOnly_User''
        )
        -- 将用户加入内置只读角色db_datareader：拥有本库所有表/视图的查询权限
        EXEC sp_addrolemember N''db_datareader'', N''ReadOnly_User'';
    '
    -- 执行上面拼接好的动态SQL语句，给当前库分配权限
    EXEC sp_executesql @sql
    -- 读取游标下一条数据库名称，进入下一轮循环
    FETCH NEXT FROM db_cursor INTO @dbName
END
-- 循环结束，关闭游标，停止读取数据库列表
CLOSE db_cursor
-- 释放游标占用的内存资源（必须操作，否则内存泄漏）
DEALLOCATE db_cursor
GO
-- 控制台打印提示文字，告知执行完成
PRINT '批量分配只读权限完成'

这段脚本看起来有点长，其实逻辑很清晰，我们拆开来看：

1. DECLARE 变量：定义两个容器，一个存库名，一个存要执行的SQL代码；
2. CURSOR 游标：把所有业务数据库拉成一个列表，挨个循环处理；
3. WHILE 循环：只要还有没处理的数据库，就一直执行权限配置；
4. USE [' + @dbName + ']：切换到当前处理的数据库，权限是库隔离的，每个库要单独配置；
5. CREATE USER：库内用户，相当于每间房门的小门权限，绑定大门钥匙（LOGIN登录）；
6. db_datareader：SQL Server内置固定只读角色，权限=仅查询所有表、视图，无增删改、无建表权限；
7. sp_addrolemember：把用户加入角色，赋予角色对应的权限；
8. CLOSE/DEALLOCATE：游标收尾，释放资源，规范写法。

第三部分：可选扩展权限脚本（逐行注释）

3.1 批量授予「查看表结构/存储过程源码」权限

-- 定义存储数据库名的变量
DECLARE @dbName SYSNAME
-- 定义存储动态SQL的变量
DECLARE @sql NVARCHAR(MAX)
-- 创建游标，读取所有业务库
DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases WHERE name NOT IN ('master','tempdb','model','msdb') AND state=0
-- 开启游标
OPEN db_cursor
-- 读取第一条库名
FETCH NEXT FROM db_cursor INTO @dbName
-- 循环处理每个库
WHILE @@FETCH_STATUS = 0
BEGIN
    -- 拼接SQL：切换到当前库，授予查看对象定义权限（能看表结构、存储过程代码，不能修改）
    SET @sql = N'USE ['+@dbName+']; GRANT VIEW DEFINITION TO ReadOnly_User;'
    -- 执行授权语句
    EXEC sp_executesql @sql
    -- 读取下一个库
    FETCH NEXT FROM db_cursor INTO @dbName
END
-- 关闭释放游标
CLOSE db_cursor
DEALLOCATE db_cursor
GO

默认情况下，只读账号只能查数据，看不到表字段定义、存储过程源码。如果对方需要了解表结构，可以加上这条授权，但仍不能修改任何对象。

3.2 授予服务器级查看性能状态权限

-- 给登录账号授予查看服务器运行状态权限：可以查慢查询、会话连接、资源占用，无数据修改权限
GRANT VIEW SERVER STATE TO ReadOnly_User;
GO

第四部分：配套运维脚本注释说明

4.1 批量查询账号在所有库的权限

DECLARE @dbName SYSNAME
DECLARE @sql NVARCHAR(MAX)
-- 遍历所有业务库
DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases WHERE name NOT IN ('master','tempdb','model','msdb') AND state=0
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
    -- 拼接查询语句：查询当前库内该用户所属角色
    SET @sql = N'
        USE ['+@dbName+'];
        -- 输出三列：数据库名、用户名、所属角色
        SELECT '''+@dbName+''' AS 数据库名, u.name 用户名, r.name 角色名
        FROM sys.database_role_members m
        JOIN sys.database_principals r ON m.role_principal_id = r.principal_id
        JOIN sys.database_principals u ON m.member_principal_id = u.principal_id
        -- 只筛选我们创建的只读账号
        WHERE u.name = N''ReadOnly_User'';
    '
    EXEC sp_executesql @sql
    FETCH NEXT FROM db_cursor INTO @dbName
END
CLOSE db_cursor
DEALLOCATE db_cursor
GO

这个脚本执行后，会列出每个库是否成功给账号分配了只读角色，用来校验前面的批量授权是否全部生效。

4.2 临时禁用/启用账号

-- 禁用登录：账号无法连接数据库，原有权限保留
ALTER LOGIN ReadOnly_User DISABLE;
GO
-- 恢复启用，注释上面那句，打开下面执行
-- ALTER LOGIN ReadOnly_User ENABLE;

4.3 批量彻底删除账号（清理脚本）

-- ========== 第一步：先删除每个数据库里的库用户 ==========
DECLARE @dbName SYSNAME
DECLARE @sql NVARCHAR(MAX)
DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases WHERE name NOT IN ('master','tempdb','model','msdb') AND state=0
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'
        USE ['+@dbName+'];
        -- 如果库内存在该用户，执行删除
        IF EXISTS(SELECT 1 FROM sys.database_principals WHERE name = N''ReadOnly_User'')
            DROP USER ReadOnly_User;
    '
    EXEC sp_executesql @sql
    FETCH NEXT FROM db_cursor INTO @dbName
END
CLOSE db_cursor
DEALLOCATE db_cursor
GO
-- ========== 第二步：删除服务器登录账号 ==========
DROP LOGIN ReadOnly_User;
GO
PRINT '账号彻底清理完成'

这里有一个关键点需要注意：必须先删所有库内用户，再删登录。如果直接删除LOGIN，会报依赖错误。

最后，总结一下整个流程的几个核心概念：

1. LOGIN（服务器登录）：实例级别，登录SQL服务的凭证，全局唯一，没有任何数据库访问权限；
2. USER（数据库用户）：单个数据库内部的身份，必须绑定LOGIN，权限只在当前库生效；
3. db_datareader 角色：数据库内置只读角色，仅 SELECT 查询，禁止 INSERT/UPDATE/DELETE/ALTER/CREATE；
4. 游标CURSOR：批量处理多数据库的工具，替代手动切换每个库重复执行创建用户的操作。

以上就是SQL Server中创建只读账号并批量分配权限的完整流程的详细内容。