SQL Server:只允许在某些存储过程上执行的权限

Question

我将以我从未负责过数据库安全开头，这对我来说都是全新的，所以如果它不好，请原谅这个问题 - 我会尝试提供尽可能多的信息。我已经搜索了周围，但没有太多关于我的具体问题的细节。

我使用的是 SQL Server 2008 R2 数据库，我需要能够限制所有域用户仅在显式定义时能够执行某些存储过程。如，在所有数据库表/视图/存储过程上拒绝选择/更新/删除/等，除了一些显式定义的存储过程(授予用户(下面的域用户)对存储过程的执行)。

我认为这对于服务器角色来说很容易，但似乎自定义服务器角色仅在 SQL Server 2012 及更高版本中可用，并且公共服务器角色授予对我所有表的选择访问权限。我读到拒绝总是优先于授予，所以我设置了一个数据库角色，该角色将选择访问权限设置为拒绝，但用户仍然可以查询表。

这是我在包含 1 个数据库的服务器上的当前设置：

服务器级别：

• 登录名：[域用户] - AD 组登录，以便所有用户都可以连接到服务器。具有服务器角色"公共"。

数据库级别：

用户

• ：[域用户] - AD 组，以便所有用户都可以连接到数据库。
• 数据库角色：[SP_Reader] - [域用户] 作为角色成员。安全对象 我将所有表设置为拒绝对选择的访问，并且我希望用户执行的存储过程设置为在执行时授予。

问题是用户仍然可以从我的表中进行选择，就好像权限不存在一样。知道我在这里做错了什么吗？

Answer 1

问题原来是除了我的自定义数据库角色 (SP_Reader) 之外，还向数据库用户分配了"db_datareader"角色，该角色仅对某些存储过程提供执行。

当我查询"sys.database_permissions"时，"db_datareader"角色没有被注意到，它不显示隐式授予的权限。

我最终找到了这个Microsoft链接，它提供了一个返回所有数据库角色成员的查询：

SELECT DP1.name AS DatabaseRoleName,   
isnull (DP2.name, 'No members') AS DatabaseUserName   
FROM sys.database_role_members AS DRM  
RIGHT OUTER JOIN sys.database_principals AS DP1  
ON DRM.role_principal_id = DP1.principal_id  
LEFT OUTER JOIN sys.database_principals AS DP2  
ON DRM.member_principal_id = DP2.principal_id  
WHERE DP1.type = 'R'
ORDER BY DP1.name;

或者，此内部存储过程返回作为参数传递的任何角色的结果：

EXEC sp_helprolemember 'db_datareader';