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
GRANT EXECUTE TO ...
for all stored procs?sp_
prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoidsp_
and use something else as a prefix - or no prefix at all!