SQL Server 2008 R2：如何限制域用户仅执行指定存储过程

刚接触数据库安全完全不用慌，这个需求其实可以通过自定义数据库角色+权限批量配置来高效实现，我给你一步步拆解：

1. 创建专属数据库角色 #

先建一个自定义角色来统一管理所有域用户的权限，避免逐个用户配置的麻烦：

USE [你的数据库名称];
GO
CREATE ROLE [DomainUserLimitedRole]; -- 角色名可以自己改，好记就行
GO

2. 拒绝角色对所有表/视图的直接操作权限 #

我们要确保这个角色的用户不能直接对任何表、视图执行SELECT/INSERT/UPDATE/DELETE，用批量脚本一次性处理所有对象：

USE [你的数据库名称];
GO

-- 拒绝角色对所有用户表的DML权限
DECLARE @sqlTables NVARCHAR(MAX) = N'';
SELECT @sqlTables += N'
DENY SELECT, INSERT, UPDATE, DELETE ON OBJECT::[' + SCHEMA_NAME(schema_id) + N'].[' + name + N'] TO [DomainUserLimitedRole];'
FROM sys.tables;
EXEC sp_executesql @sqlTables;

-- 拒绝角色对所有视图的DML权限（如果有视图需要限制的话）
DECLARE @sqlViews NVARCHAR(MAX) = N'';
SELECT @sqlViews += N'
DENY SELECT, INSERT, UPDATE, DELETE ON OBJECT::[' + SCHEMA_NAME(schema_id) + N'].[' + name + N'] TO [DomainUserLimitedRole];'
FROM sys.views;
EXEC sp_executesql @sqlViews;
GO

3. 授予角色指定存储过程的执行权限 #

把允许用户执行的存储过程权限授予这个角色，比如你要开放dbo.GetUserInfo和dbo.UpdateUserStatus：

USE [你的数据库名称];
GO
GRANT EXECUTE ON OBJECT::[dbo].[GetUserInfo] TO [DomainUserLimitedRole];
GRANT EXECUTE ON OBJECT::[dbo].[UpdateUserStatus] TO [DomainUserLimitedRole];
-- 后续加新存储过程，重复上面的GRANT语句就行
GO

4. 将域用户加入这个角色 #

把需要限制的域用户（或整个域用户组）添加到我们创建的角色里：

USE [你的数据库名称];
GO

-- 添加单个域用户
EXEC sp_addrolemember N'DomainUserLimitedRole', N'你的域名\User1';
EXEC sp_addrolemember N'DomainUserLimitedRole', N'你的域名\User2';

-- 如果要让所有域用户都受限制，直接加域用户组（比如DOMAIN\Domain Users）
EXEC sp_addrolemember N'DomainUserLimitedRole', N'你的域名\Domain Users';
GO

关键注意事项 #

• 所有权链机制：存储过程执行时默认会继承其所有者的权限，所以只要存储过程的所有者（比如dbo）有访问表的权限，用户即使没有表权限，通过存储过程也能正常操作表，这正是我们要的效果！
• 测试验证：配置完后，用一个域用户登录数据库，试试直接SELECT * FROM 某表，应该会提示权限不足；再执行授权的存储过程，应该能正常运行。
• 避免影响其他用户：不要随便修改public角色的权限，我们的自定义角色已经足够隔离域用户的权限了。

内容的提问来源于stack exchange，提问作者gbeaven