2

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
5
  • How about you use best practice -- schema.tableName instead of tableName only ?
    – Kin Shah
    Apr 27, 2018 at 21:17
  • Is most of them, or all of them? If most, what are the differences between the few that work and those that don't? Are you sure the user has the correct default database / is using the right database? Why are you granting exec to public and not the web role? Are you granting individual SP access or GRANT EXECUTE TO ... for all stored procs? Apr 27, 2018 at 21:18
  • Thanks for taking the time to respond. Keep in mind the procedure runs and returns the data in the select to the client... the error happens AFTER the select. That being said, I added the user creation process above. Apr 27, 2018 at 22:19
  • The most in the statement above is because I have not examined all of them. However, every one I looked at fails unless the user is db_owner or has been edited to add an explicit return value. Apr 27, 2018 at 22:25
  • Side note: you should not use the 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 avoid sp_ and use something else as a prefix - or no prefix at all!
    – marc_s
    May 5, 2018 at 21:02

1 Answer 1

4

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
2
  • great catch. That's certainly the problem with the way the code is presented in the question.
    – Hannah Vernon
    Apr 28, 2018 at 14:45
  • Yup. That is exactly what happened. Looks like an error going back 10 years that has been cut and pasted all over the application. Thanks for the tip! Off to clean this up. Apr 30, 2018 at 16:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.