The EXECUTE permission was denied on the object 'SPROC', database 'DATABASE', schema 'dbo'

Question

I have a few users that are unexpectedly receiving this error message today when they attempt to refresh a stored procedure via Excel:

The EXECUTE permission was denied on the object 'SPROC', database 'DATABASE', schema 'dbo'.

These operations have previously worked for months without issue and to my knowledge nothing has changed. I'm looking for a little guidance on how to track down the source of the issue.

This is a MS SQL Server 2008R2 instance with the current security settings:

• Server Login assigned to an AD group for "Domain Users" to connect to the server. The users that are experiencing this issue are a part of this AD group. I have verified each individually.
• Database role created with the AD group for "Domain Users" as a member of the role. Again, the users experiencing issues are a part of this AD group. This database role has securables (stored procedures) individually assigned so as to not grant access on all stored procedures using the following method: GRANT EXECUTE ON 'SPROC' TO 'DATABSE_ROLE'. The stored procedure that is throwing the error in Excel is listed in the securables section of the database role.

These settings allow anyone to execute only explicitly assigned stored procedures - and this has been working flawlessly until recently. Does anyone have any ideas on what to check that could be causing the error message in question?

Thanks!

-Edit- Added permissions dump:

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

Once you verified the user has GRANT or EXECUTE permission already, you may want to check there must NOT be a DENY permission applied (Directly or Indirectly) to user on respective object.

Following query would help to list out Directly or Indirectly applied permissions of a user:

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

Execute on the database for a user who has the problem:

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

Then check all permission paths to be sure they have granted EXECUTE permission and don't have denied one.