What would cause newly restored SQL Databases to show tables, but display an 'Invalid Object' error when trying to view table data?

Problem:

As an untrained and involuntary SQL DBA, I'm taking a set of SQL 2008R2 databases and restoring to a new SQL 2008R2 server in another environment.

The restore process goes fine and gives no errors. When I try to connect to them with our database explorer I see the databases and tables populate correctly. Unfortunately when I try to look at a table's data it gives me an "Invalid Object" error.

Background:

When we have performed these moves in the past for other clients, we generally run this script to "repair orphaned users". This is usually all we run and the databases then work with our db explorer and software. If we're going between SQL versions we will also change compatibility mode for the databases with a separate script

use dbar;
EXEC sp_change_users_login 'update_one', 'nurse', 'nurse';
use dbarforms;
EXEC sp_change_users_login 'update_one', 'nurse', 'nurse';
use dbdoc;
EXEC sp_change_users_login 'update_one', 'nurse', 'nurse';
use dbhistory;
EXEC sp_change_users_login 'update_one', 'nurse', 'nurse';
use dbhl7;
EXEC sp_change_users_login 'update_one', 'nurse', 'nurse';
use dbmds;
EXEC sp_change_users_login 'update_one', 'nurse', 'nurse';
use dbparadox;
EXEC sp_change_users_login 'update_one', 'nurse', 'nurse';
use dbpicklist;
EXEC sp_change_users_login 'update_one', 'nurse', 'nurse';
use dbreport; 
EXEC sp_change_users_login 'update_one', 'nurse', 'nurse';
use dbrx;
EXEC sp_change_users_login 'update_one', 'nurse', 'nurse';

Troubleshooting Steps:

I noticed that our test servers running on MSSQL all show the nurse login as having ownership of the 10 databases. In the user column it says "nurse" and in the default schema column it shows "dbo"

In the case of this server that i'm having issues with, it shows the nurse login as having ownership of the 10 databases, the user column and default schema columns BOTH say "nurse".

So my first guess was that the other environment was setup to use a schema called "nurse" and the new server doesn

It sounds like the objects are still there but are owned by the schema nurse. So for example instead of dbo.Table1 it would be nurse.Table1.

Your users aren't see the tables because they are not including the schema part of the name. If the schema is not included

SELECT * FROM Table1

Then the schema used is the default schema from the database principal (user). In this case, as you said, it's dbo which is the norm. An easy way to check this would be to do the following:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'yourtablename'

If the TABLE_SCHEMA value is nurse then that's your problem. Try changing the default_schema of your principals like this:

alter user [username] with default_schema = [nurse];

Also you could query the tables by including the two part name:

SELECT * FROM nurse.Table1

StackExchange Database Administrators Q#181761, answer score: 5