SQLServer中创建只读账号及批量分配权限的完整流程

账号统一名称：ReadOnly_User，密码 Read@123456（自行替换）

第一部分：创建服务器登录账号（实例级登录，用来登录整个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 nameFROM sys.databases-- 过滤掉4个系统内置库，不给系统库分配只读权限WHERE name NOT IN ('master','tempdb','model','msdb')-- state = 0：只筛选「在线可用」的数据库，跳过损坏/离线库AND state = 0-- 打开游标，准备开始循环读取数据库列表OPEN db_cursor-- 读取游标第一条数据，存入变量@dbNameFETCH NEXT FROM db_cursor INTO @dbName-- @@FETCH_STATUS 是系统内置函数：0=读取到数据，-1=读完所有数据退出循环WHILE @@FETCH_STATUS = 0BEGIN    -- 拼接动态SQL字符串，针对当前循环到的数据库执行权限配置    SET @sql = N'-- 切换到当前遍历到的数据库，[]包裹库名防止库名带空格/特殊字符USE [' + @dbName + '];-- 判断当前库里是否已经存在同名用户，不存在才创建，避免重复报错IF NOT EXISTS(SELECT 1 FROM sys.database_principals WHERE name = N''ReadOnly_User'')-- 在当前数据库内创建库用户，关联最开头创建的服务器登录ReadOnly_UserCREATE 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 @dbNameEND-- 循环结束，关闭游标，停止读取数据库列表CLOSE db_cursor-- 释放游标占用的内存资源（必须操作，否则内存泄漏）DEALLOCATE db_cursorGO-- 控制台打印提示文字，告知执行完成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 FORSELECT 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 = 0BEGIN-- 拼接SQL：切换到当前库，授予查看对象定义权限（能看表结构、存储过程代码，不能修改）SET @sql = N'USE ['+@dbName+']; GRANT VIEW DEFINITION TO ReadOnly_User;'-- 执行授权语句EXEC sp_executesql @sql-- 读取下一个库FETCH NEXT FROM db_cursor INTO @dbNameEND-- 关闭释放游标CLOSE db_cursorDEALLOCATE db_cursorGO

解释

默认只读账号只能查数据，看不到表字段、存储过程源码；加这条后可以看结构，仍不能修改任何对象。

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

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

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

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

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

作用

执行后会列出每个库是否成功给账号分配了只读角色，用来校验脚本是否生效。

4.2 临时禁用/启用账号

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

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

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

关键注意

必须先删所有库内用户，再删登录；如果直接删LOGIN会报依赖报错。

1. LOGIN（服务器登录）

   实例级别，登录SQL服务的凭证，全局唯一，没有任何数据库访问权限；

2. USER（数据库用户）

   单个数据库内部的身份，必须绑定LOGIN，权限只在当前库生效；

3. db_datareader 角色

   数据库内置只读角色：仅 SELECT 查询，禁止 INSERT/UPDATE/DELETE/ALTER/CREATE；

4. 游标CURSOR

   批量处理多数据库的工具，替代手动切换每个库重复执行创建用户的操作。

以上就是SQL Server中创建只读账号并批量分配权限的完整流程的详细内容，更多关于SQL Server创建只读账号并分配权限的资料请关注本站其它相关文章！

您可能感兴趣的文章:

• SqlServer创建全文索引的详细步骤
• SQL Server使用T-SQL创建数据库的操作指南
• SQL Server创建用户并授权的详细步骤记录
• SQL Server创建用户只能访问指定数据库和视图的操作步骤
• SQLServer 游标的创建和使用基本步骤