SQL Server 2008 Cannot find the object

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开发者_JAVA技巧 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.

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.

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.

I just ran into the same problem and found the solution (for my case): I had a GRANT EXECUTE ... Statement following the SQL-Code to create the procedure. Adding a GO - Statement after the Procedure-Code, but before the GRANT-Statement solved the issue.

Explanation: The GRANT EXECUTE Code was interpreted as part of the stored procedure so a user who did not have permissions the GRANT this permission received the above error message. A member of the sysadmin Group could execute the procedure without error message. But a user with only permission to execute the procedure received the error-message.

I also noticed that all the statements in the procedure were executed (by inserting print-Statments at various positions in the procedure).

I hope this helps some of you :-)