By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,272 Members | 1,454 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,272 IT Pros & Developers. It's quick & easy.

Question: re-associate dbo with sa??

P: n/a
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

Dec 27 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
Dec 28 '06 #2

P: n/a
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'... ?

Dec 28 '06 #3

P: n/a
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
Dec 28 '06 #4

P: n/a
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

Dec 28 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.