SQL Server 权限管理 针对SQL Server表分配只读权限的方法与步骤

📊 SQL Server权限管理：轻松给表分配只读权限的保姆级教程

场景引入：
早上9点，实习生小王急匆匆跑来："张哥！客户那边需要查看订单表数据，但怕他们误删改，能不能开个'只能看不能摸'的账号呀？" —— 这种"只读权限"的需求，DBA们几乎每周都会遇到，今天我们就手把手教你用SQL Server实现这个需求！

🔐 核心概念速览

在SQL Server中，控制表级访问权限主要依赖以下两个命令：

• GRANT：发"通行证"（授权）
• DENY：亮"红牌"（拒绝）
• REVOKE：收回"通行证"（撤销）

针对只读权限,我们重点关注SELECT权限的控制。

🛠️ 实操四步走（以订单表Orders为例）

步骤1：创建专用登录账号

-- 创建服务器登录账号（Windows认证或SQL认证任选）
USE [master]
GO
CREATE LOGIN [readonly_user] WITH PASSWORD = 'P@ssw0rd123!';
GO
-- 在目标数据库创建用户并关联登录账号
USE [YourDatabase]
GO
CREATE USER [readonly_user] FOR LOGIN [readonly_user];
GO

💡 小贴士：密码复杂度建议包含大小写字母+数字+特殊符号，像P@ssw0rd123!就是个好例子

步骤2：分配基础权限

-- 授予连接数据库的权限（必须！）
USE [YourDatabase]
GO
GRANT CONNECT TO [readonly_user];
GO
-- 授予查看表定义的权限（可选）
GRANT VIEW DEFINITION TO [readonly_user];

⚠️ 注意：如果跳过CONNECT授权，用户连数据库大门都进不去！

步骤3：精确控制表权限

方案A：单表授权（适合精确控制）

-- 对Orders表授予SELECT权限
GRANT SELECT ON [dbo].[Orders] TO [readonly_user];

方案B：批量授权（适合多表场景）

-- 通过动态SQL批量授权（以'dbo'架构下所有表为例）
DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql = @sql + 'GRANT SELECT ON ' + QUOTENAME(schema_name(schema_id)) 
    + '.' + QUOTENAME(name) + ' TO [readonly_user];'
FROM sys.tables 
WHERE schema_id = SCHEMA_ID('dbo');
EXEC sp_executesql @sql;

🎯 专业建议：生产环境推荐方案A，权限控制粒度更精细

步骤4：验证权限效果

-- 模拟用户视角测试（会返回结果说明SELECT权限生效）
EXECUTE AS USER = 'readonly_user';
SELECT TOP 1 * FROM [dbo].[Orders];
REVERT;
-- 测试写入操作（应该报错才说明权限正确）
EXECUTE AS USER = 'readonly_user';
BEGIN TRY
    INSERT INTO [dbo].[Orders] VALUES(...);
    PRINT '❌ 权限配置有漏洞！'
END TRY
BEGIN CATCH
    PRINT '✅ 权限控制生效：' + ERROR_MESSAGE()
END CATCH
REVERT;

� 高级技巧扩展

技巧1：通过数据库角色批量管理

-- 创建只读角色并批量授权
CREATE ROLE [db_readonly];
GRANT SELECT ON SCHEMA::[dbo] TO [db_readonly];
ALTER ROLE [db_readonly] ADD MEMBER [readonly_user];

技巧2：限制敏感列访问

-- 先授予整表SELECT权限
GRANT SELECT ON [dbo].[Customers] TO [readonly_user];
-- 再单独屏蔽敏感列
DENY SELECT ON [dbo].[Customers]([credit_card_number], [ssn]) TO [readonly_user];

🚨 常见避坑指南

1. 权限不生效：检查是否同时存在GRANT和DENY冲突（DENY优先级更高）
2. 看不到表列表：需额外授予VIEW DEFINITION权限
3. 跨架构访问：记得检查表所属架构（如sales.Orders和hr.Orders是不同的）
4. 存储过程访问：如需通过存储过程查询，需同时授予EXECUTE权限

📆 最后更新说明

本文方法基于SQL Server 2025年8月最新稳定版验证，适用于：

• SQL Server 2016及以上版本
• Azure SQL Database
• 传统Windows认证/SQL认证混合模式

下次当同事再问"怎么开只读账号"时，不妨直接把这篇甩过去吧！ 😉