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

Question

question

Angeli asked Jan 5, '11

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

sql sql-server-2008-r2

10 |1200

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

Answer 1 · 2011-01-05T07:07:27Z

Leo answered Jan 5, '11

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

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

Answer 2 · 2011-01-06T06:13:38Z

KenJ answered Jan 6, '11

ALTER AUTHORIZATION ON SCHEMA:: TO ALTER USER WITH DEFAULT_SCHEMA = [ http://msdn.microsoft.com/es-es/library/ms187359.aspx][1] [ http://msdn.microsoft.com/es-es/library/ms187359.aspx][2] [1]: http://msdn.microsoft.com/es-es/library/ms187359.aspx [2]: http://msdn.microsoft.com/en-us/library/ms176060.aspx

10 |1200

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

question