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
Best Answer
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 theCREATE PROCEDURE
statement is executed (i.e.: the nextGO
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 followingGRANT
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 theGRANT
with no problem.So, simply remove the
GRANT
from the procedure definition, and handle permissions in a separate batch or script: