Root cause of an "Invalid object name: dbo.etc" error?

Question

I'm doing some maintenance programming on a fairly large project, which was started by someone who's now left the company...

I've just backed up one of the company's databases, and then reattached it to our test server. That much appears to be working okay.

I then go through the program's usual login procedure, and that part also appears to work.

However, once I get to a point in the program where it needs to execute a stored procedure, I get back an error telling me Invalid object name 'Informix.dbo.customer'.

Running the same function on the original database works fine, and returns the data I expect to see.

Explanations for similar errors I've found seem to refer to Schemas, but that's where things get a little odd. The original database doesn't obviously have any Schemas; In its "Security" folder it just has a "Users" folder, containing dbo, and a "Roles" folder, containing the "Database Roles" folder, with the usual db_owner, etc. stuff, and an empty folder named "Application Roles".

The Security folder on the backed-up-and-restored database is full of all kinds of crap. Three users in addition to dbo, a "Schemas" folder, "Certificates" folder, two encryption key folders... I can't delete any of these.

From my limited understanding of the SQL login system, the user I'm logging in as is getting non-dbo-permissions from this collection of random crap, and so is being denied access to the parts of the database owned by dbo.

For my own understanding, what is the core of the problem that's throwing up these Invalid object name errors? And for practical matters, what can I do to rectify this situation and actually have the program I'm using work on the test database in the same way as it does on the live one?

Answer 1

If I understand correctly, you're executing a procedure (SomeProc) in a database (SomeDB) and it's giving the error Invalid object name 'Informix.dbo.customer'? That simply means that SomeProc cannot find an object called "customer" in a schema called "dbo" in a database called "Informix". There are several possible reasons for this:

1. The object doesn't exist, possibly because the schema and/or database don't exist
2. The object exists, but the user running the procedure doesn't have permission to even see it
3. The object exists, but the database is case-sensitive and some part of the name doesn't match the name in your code

You'll need to investigate more to find out what the cause is in your case, but as a complete guess, your production server has both the Informix and SomeDB databases, but your test server has only SomeDB?

Finally, when posting questions please always include your SQL Server version (2000/2005/2008) and edition (Express, Standard, Enterprise); they can be very important when talking about schemas and permissions, because features and behaviour can be different.