NEWBIE: How to change owner of db to an existing table owner?

Dear MS SQL Experts,

I want to make "Bob" the owner of a database, in which "Bob" is already the owner of tables in the database, and "Bob" also has all available authorities over the database.  When I try:

use MyDB
go
EXEC sp_changedbowner 'Bob;

I get this error:

Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 46
The proposed new database owner is already a user in the database.

Is there an easy way to make this happen?

Thanks,
BrianMc1958
BrianMc1958Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BrianMc1958Author Commented:
Slight typo in original post (missing close quote):

use MyDB
go
EXEC sp_changedbowner 'Bob';
0
Aneesh RetnakaranDatabase AdministratorCommented:
ok,

try creating a templogin and change the ownership to that user first,
after that u change the ownership to 'Bob'


USE urDatabase
GO
EXEC sp_addlogin 'TempOwner'
EXEC sp_changedbowner 'TempOwner'
EXEC sp_changedbowner 'Bob'
EXEC sp_droplogin 'TempOwner'
GO
0
Scott PletcherSenior DBACommented:
You can try this "hack" -- no guarantees, but I've heard it works :-)

USE master
GO

EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE

UPDATE sysdatabases
SET sid = (SELECT sid FROM syslogins WHERE name = N'Bob')
WHERE name = N'MyDB'

EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

BrianMc1958Author Commented:
I'm afraid I still have the same problem, although TempOwner now owns the Db.  The full response was:

New login created.
The dependent aliases were mapped to the new database owner.
Database owner changed.
Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 46
The proposed new database owner is already a user in the database.
Server: Msg 15174, Level 16, State 1, Procedure sp_droplogin, Line 62
Login 'TempOwner' owns one or more database(s). Change the owner of the following database(s) before dropping login:

Any other ideas?

Thanks,
BrianMc1958
0
Scott PletcherSenior DBACommented:
Naturally the name on the "syslogins" subquery is the same as the *login* name -- most often the login name is the same as the user name, but that is not required, so verify it in your specific situation.
0
Scott PletcherSenior DBACommented:
I think you have to "hack" or go the route of removing 'Bob' first, which will be a real pain since that id owns objects in the db.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BrianMc1958Author Commented:
Please save me from real pain!  I hate real pain!  Give me a magic pill !

--BrianMc1958
0
BrianMc1958Author Commented:
Dear ScottPletcher,

We crossed in the email.  I'm afraid I didn't see the hack you actually posted until now.  Although it's painful, I do know a long way around that will solve the problem with less risk (I'm a SQL newbie), and I should probably do that.  Thank you (everyone) for your help anyway...

--BrianMc1958
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.