We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

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

BrianMc1958
BrianMc1958 asked
on
Medium Priority
1,548 Views
Last Modified: 2008-02-01
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
Comment
Watch Question

Author

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

use MyDB
go
EXEC sp_changedbowner 'Bob';
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
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
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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

Author

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
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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.
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Please save me from real pain!  I hate real pain!  Give me a magic pill !

--BrianMc1958

Author

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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.