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.

• gbeaven: I tested a few users that are known to be having the issue and a couple that aren't having issues and all return the same for permission paths: 'Domain\Domain Users'