Question: re-associate dbo with sa??

[BD]

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

[Dan Guzman]

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" <ro*********@gmail.comwrote in message
news:11**********************@f1g2000cwa.googlegro ups.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

[BD]

USE MyDatabase
EXEC sp_changedbowner 'sa'

--
Hope this helps.

Thanks, Dan - I expect it will.

Out of curiosity, though - I presume that dbo is unique in this regard
- ie., if the same thing had happened with a different ID, I'd have to
reassociate it 'the hard way'... ?

[Erland Sommarskog]

BD (ro*********@gmail.com) writes:

Out of curiosity, though - I presume that dbo is unique in this regard
- ie., if the same thing had happened with a different ID, I'd have to
reassociate it 'the hard way'... ?

Yes and no. That is dbo is he only you can fix with sp_changedbowner.
The others you can fix with sp_change_users_login. This is a little
less painful and dropping the users, since you don't lose permissions
and that.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[BD]

The others you can fix with sp_change_users_login.

Oh, perfect. Thanks for that. I will find that very useful, as it turns
out there are several orphaned users on this system.

Cheers,

BD