question

Angeli avatar image
Angeli asked

How to move a database to another server where schema is not dbo

I am trying to move a database where the user and schema owner is not dbo but something else say I executed a script to create the login on the new server with exactly the same SID as the one in the source server. Then copied the data and log file to other server and attached the same. However, when I now login as I cannot access the objects without giving the schema name explicitly. The source db server is SQL 2008 and the destination is SQL 2008 R2 What am I doing wrong? Any help will be welcome thank you
sqlsql-server-2008-r2
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Leo avatar image
Leo answered
Did you try by doing the following - 1.Backup DB on Server 2008 and Restore on R2 2.Drop the User1 on R2 and Re-apply the user and schema
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.