SQL Server权限设置的几种方法小结

1. 服务器级权限

管理登录账户对整个SQL Server实例的访问权限。

-- 创建登录账户
CREATE LOGIN [用户名] WITH PASSWORD = '密码';

-- 授予服务器角色
ALTER SERVER ROLE [sysadmin] ADD MEMBER [用户名];
-- 或使用更细粒度的权限
GRANT CONNECT SQL TO [用户名];
GRANT VIEW SERVER STATE TO [用户名];

2. 数据库级权限

管理用户对特定数据库的访问。

-- 在目标数据库中创建用户并关联登录
CREATE USER [用户名] FOR LOGIN [用户名];

-- 授予数据库角色
ALTER ROLE [db_owner] ADD MEMBER [用户名];
-- 或自定义数据库角色
CREATE ROLE [自定义角色];
GRANT SELECT, INSERT, UPDATE ON SCHEMA::dbo TO [自定义角色];
ALTER ROLE [自定义角色] ADD MEMBER [用户名];

3. 对象级权限

控制对表、视图、存储过程等特定对象的访问。

-- 表权限
GRANT SELECT, INSERT ON [表名] TO [用户名];
GRANT UPDATE ON [表名](列1, 列2) TO [用户名]; -- 列级权限
GRANT DELETE ON [表名] TO [用户名];

-- 存储过程权限
GRANT EXECUTE ON [存储过程名] TO [用户名];

-- 视图权限
GRANT SELECT ON [视图名] TO [用户名];

4. 架构级权限

通过架构管理一组对象的权限。

-- 授予架构权限
GRANT SELECT, INSERT, UPDATE ON SCHEMA::[架构名] TO [用户名];

-- 控制架构所有权
ALTER AUTHORIZATION ON SCHEMA::[架构名] TO [用户名];

5. 使用角色管理权限

最佳实践是通过角色来管理权限。

-- 创建自定义角色
CREATE ROLE [报表角色];

-- 为角色分配权限
GRANT SELECT ON SCHEMA::Sales TO [报表角色];
GRANT EXECUTE ON [sp_生成报表] TO [报表角色];

-- 将用户添加到角色
ALTER ROLE [报表角色] ADD MEMBER [用户名1];
ALTER ROLE [报表角色] ADD MEMBER [用户名2];

6. 拒绝权限

使用DENY明确拒绝特定权限（优先级最高）。

DENY DELETE ON [敏感表] TO [用户名];
DENY ALTER ON SCHEMA::dbo TO [公共角色];

7. 使用存储过程执行权限

通过EXECUTE AS实现更精细的控制。

-- 创建带执行上下文的存储过程
CREATE PROCEDURE [敏感操作]
WITH EXECUTE AS '具有权限的用户'
AS
BEGIN
    -- 需要高权限的操作
END;

-- 只授予执行该过程的权限
GRANT EXECUTE ON [敏感操作] TO [普通用户];

8. 使用证书和密钥的权限

高级安全场景下使用。

-- 创建证书和用户
CREATE CERTIFICATE [证书名]
CREATE USER [证书用户] FROM CERTIFICATE [证书名];

-- 分配权限
GRANT CONTROL ON DATABASE::[数据库名] TO [证书用户];

权限查询语句

-- 查看用户权限
EXEC sp_helprotect NULL, '用户名';

-- 查看登录账户权限
SELECT * FROM sys.server_permissions;
SELECT * FROM sys.server_principals;

-- 查看数据库用户权限
SELECT * FROM sys.database_permissions;
SELECT * FROM sys.database_principals;

-- 查看角色成员
EXEC sp_helprolemember;

最佳实践建议

权限优先级顺序

掌握这些权限设置方法可以帮助您构建安全的SQL Server环境，确保数据的保密性、完整性和可用性。