Object Ownership

Last Post 10 Jul 2009 02:03 PM by d_spears. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
d_spears
New Member
New Member

--
10 Jul 2009 08:31 AM
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!

regards,

d_spears
d_spears
New Member
New Member

--
10 Jul 2009 01:59 PM

This actually works well. Thanks for this shortcut. However, this feature will be dropped in a future release of SQL. It is suggested to use Alter User. I tried this and the script runs when I try to alter user (db) sysop to a user of the same (login name) name sysop but still no access. I then also have to run this statement a second time to alter the schema. Any thoughts?

Thanks again

d_spears
d_spears
New Member
New Member

--
10 Jul 2009 02:03 PM
Copying the user is a great idea, but Server A and and Server B are not connected.

Thanks
You are not authorized to post a reply.

Acceptable Use Policy