Posted by BD on 12/27/06 23:36

Hi all.

Running SQL2K SP4 on W2K3 Standard, SP4.

I have just refreshed a database on one server with a backup from
another. The database had existed previously on the target server, and
I am just refreshing its contents. I used the following approach

1) From the target server, create a SQL script with users and roles
2) From the source server, back up the db
3) Transfer the file
4) Restore the db, checking the location of the data and log files to
ensure correctness
5) Remove orphaned users
6) Run in the users/roles script generated in step 1
7) Run in a canned script containing object-level grants.

I've done this dozens of times in other databases, but something a
little unusual has occurred here:

My dbo user is orphaned. Normally it's associated with the 'sa' login,
but at this point there is no associated user.

I can see why this has happened - on the source db, the dbo user has
been associated with a non-default login.

And, many of the objects in the db are owned by dbo.

The only viable option I see is to change the ownership of these
objects, drop dbo, re-add it with the association to sa, and reassert
the object-level grants.

But what I would prefer is some way to change the association of the
dbo user, to associate it with 'sa' without the need to drop and
recreate.

Can anyone suggest an alternative strategy to associate a login and a
user after-the-fact?

Thanks much for all input!

BD

[Back to original message]