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

Question

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

Answer 1

What has happened here is that you have inadvertently included the GRANT statement in your stored procedure definition.

Most people assume that the BEGIN/END block contains the definition of the stored procedure, but it is actually through the end of the batch in which the CREATE PROCEDURE statement is executed (i.e.: the next GO or the end of the script, whichever comes first.)

In my experience, it is common to omit a GO between the stored procedure definition and the following GRANT statements in build scripts. I've done it myself! And it often goes unnoticed because a lot of people run their apps with db_owner role membership, and db_owner can execute the GRANT with no problem.

So, simply remove the GRANT from the procedure definition, and handle permissions in a separate batch or script:

CREATE PROCEDURE [dbo].[sp_fakeProcedureName] 
AS
BEGIN
  SET NOCOUNT ON;

  SELECT    fakeTablePKIdentity, column2, column3, column4 
  FROM fakeTable
  ORDER BY column2
END
GO