Sql-server – The EXECUTE permission was denied on the object ‘SPROC’, database ‘DATABASE’, schema ‘dbo’

excelpermissionssql serversql-server-2008-r2t-sql

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

Best Answer

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.

Related Solutions

Sql-server – DENY EXECUTE on one schema object hides other objects in same schema

Since you didn't specify any other permissions you gave or denied to the user group AD\Users, I'll assume that they are memebers of db_datareader and public and have no other specific permissions.

To perform my test, I used SQL Server 2008, not R2 and started out with a completely new user group, database, schema and stored procedures.

As sysadmin

Created a Windows Group called AD\TEST_USERS in Windows

Created a login from that Windows Group in SQL Server

Created a test database TEST

Added AD\TEST_USERS as a member of public and db_datareader on the TEST database

Created a schema called XTR

Added two stored procedures sp_1 and sp_2 to XTR

As a member of AD\TEST_USERS

Opened up SSMS

Opened up the TEST database and expanded stored procedures. Nothing was visible

As sysadmin

GRANT EXECUTE ON OBJECT::XTR.sp_1 TO [AD\TEST_USERS]
GRANT EXECUTE ON OBJECT::XTR.sp_2 TO [AD\TEST_USERS]

As a member of AD\TEST_USERS

I refreshed the stored procedures and now see XTR.sp_1 and XTR.sp_2

As sysadmin

DENY EXECUTE ON OBJECT::XTR.sp_1 TO [AD\TEST_USERS]

As a member of AD\TEST_USERS

I refresh stored procedures in SSMS and no longer see XTR.sp_1 but I do see XTR.sp2

XTR stored procedures

Doing the same steps as you on my SQL Server 2008, simply denying EXECUTE on one stored procedure did not make the other stored procedure invisible in the same schema to members of my test group.

Sql-server – How to detect execute permission granted to a role (when no ON clause was used)

GRANT EXECUTE TO [principal] is simply a shortcut for GRANT EXECUTE ON DATABASE::<dbname> TO [principal];

You can check this using the following:

SELECT dp.name
    , perms.class_desc
    , perms.permission_name
    , perms.state_desc
FROM sys.database_permissions perms
    INNER JOIN sys.database_principals dp ON perms.grantee_principal_id = dp.principal_id 
WHERE dp.name = 'MyRole'

Best Answer

Related Solutions

Sql-server – DENY EXECUTE on one schema object hides other objects in same schema

Sql-server – How to detect execute permission granted to a role (when no ON clause was used)

Related Question