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