I am trying to determine the correct way to restore a database from one SQL server to another and maintain object ownership. EX: Server A has a database (XYZ) where all tables are owned by the user sysop. I make a backup of the database and restore it to Server B. Before the restore I create the user sysop on Server B. However after the restore on Server B the new user does not have access to the DB and I can not add the user because it already exists. As a work around I run a script that drops and re-adds sysop as table owner on all tables. This works, but is there a better way?
Thanks in advanced!