Cannot find the object "XXX" because it does not exist or you do not have permission.

Question

I'm getting the following error when calling a stored procedure:

Cannot find the object "XXX" because it does not exist or you do not have permission.

I've checked the database and the SP is there with the correct permissions yet I'm still getting the error.

Any suggestions?

Answer 1

The account that you are using when calling the stored procedure must not be the same account that you are using to check it. Make sure that the account that you are using to execute the sproc has access to the object.

Answer 2

Always use the dbo. (or other schema) prefix both when creating and when accessing objects.

I wrote about this very topic recently:

http://sqlblog.com/blogs/aaron%5Fbertrand/archive/2009/10/11/bad-habits-to-kick-avoiding-the-schema-prefix.aspx

Answer 3

As well as other answers about schema/security etc:

• do you have a DENY on it somewhere?
• case sensitive object names and using "wrong" name?
• wrong database context? eg OtherDB.dbo.Myproc

Answer 4

Ok, here's what happened. There was a special character before the end of the SP so it was incomplete yet still valid, somehow.

So I could see the SP and see the permissions on it but I could not run it. So to solve the issue I had to copy the text out of SQL Management Studio and paste it into Notepad, then remove the special character, then copy and paste it back into SQL Management Studio and run the alter script.

Very strange how the character got there!