SQL Server 2008 Cannot find the object

Question

I've created a number of tables and stored procedures. There is a login that is assigned to a role that has execute permission. For all but 2 of the stored procedures, everything is working just fine. But, for 2 of them I get the following error:

Msg 15151, Level 16, State 1, Procedure XX, Line 15
Cannot find the object 'XX', because it does not exist or you do not have permission.

I've checked the procedure exists and that the role has execute permission on it. If I login in to the database with the login, I can see the procedure and execute it, but I get the results in the results tab of the query window and the error above in the Messages tab of the query window.

I've been looking at this for a couple of days now and not getting anywhere. Any help would be greatly appreciated.

Answer 1

A couple things to check.

First, if you have multiple schemas in your database it is a good idea to make sure you use the schema when calling them. For example, if you have a schema named Accounts and a proc named AccountGet then you should use exec accounts.AccountGet ..

Second, if your procedure uses ANY dynamic sql, then the calling user will need to have the appropriate rights to those tables, not just to the procedure.

Let's say you have a procedure named AccountGet which performs an exec that does a select. Your user will need to have rights to execute AccountGet as well as the select right on the table(s) in question.

The same goes for making any calls which require a security context. The context of the currently executing user is passed on. If that user doesn't have rights, then you'll get permission errors.

Answer 2

I found the problem. I was creating the procedure from a script. I think there was some kind of weird hidden character in the script. I typed it all out again and recreated it from there and it worked just fine. What a really weird problem and a really misleading error message.