Moving Database That Has a Schema

Question

Moving Database That Has a Schema

dbaforever

Hall of Fame

Points: 3740
More actions
August 1, 2013 at 8:04 pm

#278168

Hi experts,
I'm trying to move a 2008 database that has a named schema (not dbo) to another server.
I restored from backup onto the new server. This database is the default for user abc. Login abc is also the owner of schema abc.
On the new server the abc login this database uses works, sort of, but is unable to access the tables which are owned by the schema ie abc.Table1
unless I explicitly add the schema to the Select statement like this:
Select * from abc.Table 1 - this works.
2 things are wrong:
1.Objects created by the abc login should automatically have the abc schema, but they don't - they have dbo
2. This login cannot select data unless the schema is included in the Select statement - as Select * from abc.Table1. If I use Select * from Table1, I get "object not found".
Can anyone tell me what's wrong please?
I'm thinking of starting over.
Should I pre-create the login BEFORE restoring or should I delete the login, then restore this db?
Thanks, Jack

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Were the schema and user a part of the database on the old server before you backed it up and restored it to this new server?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

dbaforever Hall of Fame Points: 3740 More actions · Answer 2

Yes, Jason. The schema and the login have the same name and they were in the old database, everything worked fine.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

did you create the login abc using the same sid as the login from the previous server?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ScottPletcher SSC Guru Points: 101120 More actions · Answer 4

First, you have to be careful with terminology in SQL Server -- a login and a user are not the same thing.

The login is at the server level, the user is at the db level.

Is the login for a domain account or a SQL account?

If it's for a domain account, simply add the login before you restore the database and SQL will automatically link up the login with the user when you restore.

If it's for a SQL account, and you want the login to automatically link up with the user when you restore a db, then add the login to the new server with the same sid as it had on the old server.

Here's how:

1) on the old server, run command "EXEC sp_helpuser" in the db, and copy the SID value.

2) on the new server, run the CREATE LOGIN command, (drop the existing login first if it exists), but use the "SID = <value>" clause, specifying the SID value from the old server.

Then restore the db on the new server.

Finally, check the default schema for the user on the new server, and make sure it is "abc". It sounds like the default user was "abc" on the old server but is somehow now "dbo" on the new server.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".