SQL Server: what additional permissions for EXECUTE for implicit result codes?

2 votes

1 answer

2423 views

                          Tested on: 2014+SP2+CU7, 2016+SP1+CU5, 2016+SP2

Short description: User with EXECUTE permission still gets

> Cannot find the object 'sp_fakeProcedureName', because it does not exist or you do not have permission.

when successfully EXECuting the stored procedure (example below).

We are finally moving application access from db_owner to a more limited role. Everything has been fine with the user as a member of db_owner. We crated a new role db_webAppAccess and granted SELECT, UPDATE, DELETE, and EXECUTE. The problem occurs when the user executes most if not all of our stored procedures. The procedure runs, but at the very end fails with 

> Msg 15151, Level 16, State 1, Procedure sp_fakeProcedureName, Line 14 [Batch Start Line 2]     
> Cannot find the object 'sp_fakeProcedureName', because it does not exist or you do not have permission.

We know it executes because when performed in SSMS the expected data is returned. Also, if we cope the contents of the stored procedure and put it into SSMS it executes without error.

Now to the confusing part: if we add an explicit return value, the error disappears. Ex add return(0) as the line of the SP below. Sadly, we have a few hundred stored procedures that would need to be examined and set to have explicit return values in all cases.

I am at an absolute loss on exactly what is different in executing a stored procedure when there is and is not an explicitly defined return code. Is there some additional grant I can give to allow access to the result codes?

Thank you for any help you can provide!


    CREATE PROCEDURE [dbo].[sp_fakeProcedureName] 
    AS
    BEGIN
      SET NOCOUNT ON;
    
      SELECT	fakeTablePKIdentity, column2, column3, column4 
	  FROM fakeTable
      ORDER BY column2
    END


    GRANT EXEC ON [sp_fakeProcedureName] TO PUBLIC
    GO

Here is the procedure I used to setup the role and user.

    USE [fakeDatabse]
    GO

    CREATE USER [usr_fakeUser] FOR LOGIN [DOMAIN\fakeUser] WITH DEFAULT_SCHEMA=[dbo]
    GO

    CREATE ROLE [db_fakeRole]
    GRANT CONNECT TO [db_fakeRole] AS [dbo]
    GRANT DELETE TO [db_fakeRole] AS [dbo]
    GRANT EXECUTE TO [db_fakeRole] AS[dbo]
    GRANT INSERT TO [db_fakeRole] AS [dbo]
    GRANT SELECT TO [db_fakeRole] AS [dbo]
    GRANT UPDATE TO [db_fakeRole] AS [dbo]

    ALTER ROLE [db_fakeRole] ADD MEMBER [usr_fakeUser]
    GO


                        

Asked by Joe Mroczek (23 rep)

Apr 27, 2018, 09:10 PM
Last activity: May 5, 2018, 09:02 PM

SQL Server: what additional permissions for EXECUTE for implicit result codes?

Related Questions