对象“SPROC”、数据库“数据库”、模式“dbo”上的执行权限被拒绝

Question

今天，有几个用户在试图通过Excel刷新存储过程时意外地收到了此错误消息：

对象“SPROC”、数据库“数据库”、模式“dbo”上的EXECUTE权限被拒绝。

这些手术在过去几个月里一直没有问题，据我所知，没有任何改变。我正在寻找一个关于如何追踪问题的来源的小指导。

这是一个具有当前安全设置的MSSQLServer2008R2实例：

• 服务器登录分配给AD组，以便“域用户”连接到服务器。正在经历此问题的用户是此AD组的一部分。我已经逐个核实过了。
• 使用AD组为“域用户”创建的数据库角色作为角色的成员。同样，遇到问题的用户也是这个AD组的一部分。此数据库角色具有单独分配的安全性(存储过程)，以便不使用以下方法授予对所有存储过程的访问权限：GRANT EXECUTE ON 'SPROC' TO 'DATABSE_ROLE'。在Excel中抛出错误的存储过程列在数据库角色的securables部分中。

这些设置允许任何人只执行显式分配的存储过程--直到最近，这一直是完美无缺的。有没有人想过检查哪些内容可能会导致错误消息的产生？

谢谢!

-编辑添加的权限转储：

name                class  class_desc       permission_name  state_desc
dbo                 0      DATABASE         CONNECT          GRANT
DOMAIN\Domain Users 0      DATABASE         CONNECT          GRANT
DOMAIN\USER1        0      DATABASE         CONNECT          GRANT
DOMAIN\USER2        0      DATABASE         CONNECT          GRANT
guest               1      OBJECT_OR_COLUMN EXECUTE          DENY
guest               1      OBJECT_OR_COLUMN EXECUTE          DENY
guest               1      OBJECT_OR_COLUMN EXECUTE          DENY
guest               1      OBJECT_OR_COLUMN EXECUTE          DENY
guest               1      OBJECT_OR_COLUMN EXECUTE          DENY
guest               1      OBJECT_OR_COLUMN EXECUTE          DENY
guest               1      OBJECT_OR_COLUMN EXECUTE          DENY
IM_Everyone         1      OBJECT_OR_COLUMN EXECUTE          GRANT
IM_Everyone         1      OBJECT_OR_COLUMN EXECUTE          GRANT
IM_Everyone         1      OBJECT_OR_COLUMN EXECUTE          GRANT
IM_Everyone         1      OBJECT_OR_COLUMN EXECUTE          GRANT
IM_Everyone         1      OBJECT_OR_COLUMN EXECUTE          GRANT
IM_Everyone         1      OBJECT_OR_COLUMN EXECUTE          GRANT
IM_Everyone         1      OBJECT_OR_COLUMN EXECUTE          GRANT
IM_Everyone         1      OBJECT_OR_COLUMN EXECUTE          GRANT
IM_Everyone         1      OBJECT_OR_COLUMN EXECUTE          GRANT
IM_Everyone         1      OBJECT_OR_COLUMN EXECUTE          GRANT
IM_Everyone         1      OBJECT_OR_COLUMN EXECUTE          GRANT
IM_Everyone         1      OBJECT_OR_COLUMN EXECUTE          GRANT
IM_Everyone         1      OBJECT_OR_COLUMN EXECUTE          GRANT
IM_Everyone         1      OBJECT_OR_COLUMN EXECUTE          GRANT
IM_Everyone         1      OBJECT_OR_COLUMN EXECUTE          GRANT
IM_Everyone         1      OBJECT_OR_COLUMN EXECUTE          GRANT
IM_Everyone         1      OBJECT_OR_COLUMN EXECUTE          GRANT
IM_Everyone         1      OBJECT_OR_COLUMN EXECUTE          GRANT
IM_Everyone         1      OBJECT_OR_COLUMN EXECUTE          GRANT
IM_Everyone         1      OBJECT_OR_COLUMN EXECUTE          GRANT
IM_Everyone         1      OBJECT_OR_COLUMN EXECUTE          GRANT
IM_Everyone         1      OBJECT_OR_COLUMN EXECUTE          GRANT
IM_Everyone         1      OBJECT_OR_COLUMN EXECUTE          GRANT
IM_Everyone         1      OBJECT_OR_COLUMN EXECUTE          GRANT
IM_Reader           1      OBJECT_OR_COLUMN EXECUTE          GRANT
IM_Reader           3      SCHEMA           SELECT           GRANT
IM_Reader           3      SCHEMA           SELECT           GRANT
NT AUTHORITY\SYSTEM 0      DATABASE         CONNECT          GRANT
public              1      OBJECT_OR_COLUMN EXECUTE          GRANT
public              1      OBJECT_OR_COLUMN EXECUTE          GRANT
public              1      OBJECT_OR_COLUMN EXECUTE          GRANT
public              1      OBJECT_OR_COLUMN EXECUTE          GRANT
public              1      OBJECT_OR_COLUMN EXECUTE          GRANT
public              1      OBJECT_OR_COLUMN EXECUTE          GRANT
public              1      OBJECT_OR_COLUMN EXECUTE          GRANT
ReportReader        0      DATABASE         CONNECT          GRANT
SSRS_Reader         0      DATABASE         CONNECT          GRANT
SSRS_Reader_S2      0      DATABASE         CONNECT          GRANT
SSRS_Reader_S2      0      DATABASE         EXECUTE          GRANT

Answer 1

一旦您验证了用户已经拥有GRANT或EXECUTE权限，您可能希望检查在各自的对象上不存在应用于用户(直接或间接)的DENY权限。

以下查询将有助于列出用户直接或间接应用的权限：

Declare @UserName varchar (100) = 'username';

-- Permission applied to user (directly) -------------------------------------------------------------

select d.name, dp.* 
from sys.database_permissions as dp
        join sys.database_principals as d on dp.grantee_principal_id = d.principal_id
where d.name = (@UserName) --and dp.state = 'D'
order by d.principal_id

-- Permission applied to user (indirectly) -------------------------------------------------------------
select  dm.name as DB_UserName,
        sp.name as LoginName,
        dr.name as DB_RoleName,
        dp.[permission_name],
        dp.type,
        dp.state_desc
from sys.database_principals as dm  
        join sys.database_role_members as drm on dm.principal_id = drm.member_principal_id
        join sys.database_principals as dr on drm.role_principal_id = dr.principal_id
        left join sys.server_principals as sp on dm.sid = sp.sid
        left join sys.database_permissions as dp on dr.principal_id = dp.grantee_principal_id
Where (dm.name = @UserName or sp.name = @UserName) --and dr.name like 'db_deny%'
go

Answer 2

对有问题的用户在数据库上执行：

 EXEC sys.xp_logininfo [DOMAIN\user], 'all'

然后检查所有权限路径，以确保它们已经授予了执行权限，并且没有拒绝一个权限。