對象“SPROC”、數據庫“DATABASE”、模式“dbo”的 EXECUTE 權限被拒絕

我有一些使用者今天嘗試通過 Excel 刷新儲存過程時意外收到此錯誤消息：

對象“SPROC”、數據庫“DATABASE”、模式“dbo”的 EXECUTE 權限被拒絕。

這些操作之前已經執行了幾個月沒有問題，據我所知，沒有任何改變。我正在尋找有關如何追踪問題根源的一些指導。

這是具有目前安全設置的 MS SQL Server 2008R2 實例：

• 伺服器登錄分配給“域使用者”的 AD 組以連接到伺服器。遇到此問題的使用者屬於此 AD 組。我已經分別驗證了每一個。
• 使用 AD 組為“域使用者”創建的數據庫角色作為角色的成員。同樣，遇到問題的使用者是該 AD 組的一部分。此數據庫角色具有單獨分配的安全對象（儲存過程），以便不使用以下方法授予對所有儲存過程的訪問權限：GRANT EXECUTE ON 'SPROC' TO 'DATABSE_ROLE'. 在 Excel 中引發錯誤的儲存過程列在數據庫角色的安全對象部分中。

這些設置允許任何人只執行顯式分配的儲存過程 - 直到最近這一直完美無缺。是否有人對檢查可能導致相關錯誤消息的內容有任何想法？

謝謝！

-Edit- 添加權限轉儲：

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

為有問題的使用者在數據庫上執行：

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

然後檢查所有權限路徑以確保它們已授予 EXECUTE 權限並且沒有拒絕任何權限。

一旦您驗證了使用者已經擁有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

引用自：https://dba.stackexchange.com/questions/216801