5

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't have it. I tried to create a new schema called "nurse" in one of the databases and I get this error:

enter image description here

I then tried this on a few tables to see if it worked... no such luck

ALTER SCHEMA dbo TRANSFER nurse.Table1 <--- This actually did fix my issue. I had it backwards before.

My main issue is that I don't even know what the root problem is, just that it doesn't work. I've talked to our developers and others who have some SQL experience and they all were stumped.

Any support would be greatly appreciated. Thanks in advance!

~A-A-Ron

  • Sounds like a client error, make sure your version of SSMS is at least as updated as the server you are connecting to. (Help, About...). I'd guess the second box has a later service pack/CU than the first/your client. – BradC Jul 25 '17 at 18:59
  • The solution ended up being the ALTER SCHEMA ___ TRANSFER ___ – Aaron Jul 26 '17 at 13:41
5

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
  • Thank you! Between this and some old documentation, I think I have it resolved. We have an old utility we use to run a query on all objects within an alias. I did the ALTER SCHEMA dbo TRANSFER nurse.%s in our utility and it changed all the tables to dbo. within that alias. Once I do this for the other 9 Databases I should be in business. Thank you! – Aaron Jul 25 '17 at 19:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.