CODEWITHSUNDEEP
sql-serverpermissions
cedd
cedd

Reputation: 1861

"Cannot find the object [stored proc] because it does not exist or you do not have permissions", when it definitely does exist and have permissions

I have a stored procedure (below) that definitely exists and I am trying to execute it as a user which definitely has permissions. I checked this more times than was necessary. But I still get the following error

Cannot find the object [stored proc] because it does not exist or you do not have permissions

I found that if I removed permissions, than I got a more accurate message stating that I did not have execute permissions.

I tried scripting the stored procedure and giving it a different name, but this didn't help either.

CREATE PROCEDURE [dbo].[Child_Get] 
    @ID int
AS
BEGIN
    SELECT * FROM [tblChilds] WHERE [ID] = @ID
END
GO

GRANT EXECUTE ON [dbo].[Child_Get] to 'BLAH\NT4Name'
GO

Upvotes: 1

Views: 2695

Answers (1)

cedd
cedd

Reputation: 1861

This was very weird, but the problem was the "GRANT EXECUTE ..." statement after the stored procedure. After I removed this line everything worked fine.

CREATE PROCEDURE [dbo].[Child_Get] @ID int
AS
BEGIN
    SELECT * FROM [tblChilds] WHERE [ID] = @ID
END
GO

I imagine that the "GRANT EXECUTE ...." somehow magically gets included in the stored proc (despite being outside of the BEGIN / END and after a GO). This means that some users have permissions defined to execute the sproc, but do not have enough permissions to "GRANT EXECUTE ...", and this leads to the very misleading "Cannot find the object [storedproc] because it does not exist or you do not have permissions" error message.

Upvotes: 3

Related Questions