|
|
Posted by Dan Guzman on 12/28/06 02:39
> 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.
The login mapping for the 'dbo' user is determined by database ownership.
You can execute sp_changedbowner to change/fix the database owner:
USE MyDatabase
EXEC sp_changedbowner 'sa'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"BD" <robert.drea@gmail.com> wrote in message
news:1167262606.363325.184160@f1g2000cwa.googlegroups.com...
> 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
>
Navigation:
[Reply to this message]
|