Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2006-03-27
8
Medium Priority
?
1,449 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
0
Comment
Question by:BrianMc1958
  • 4
  • 3
8 Comments
 

Author Comment

by:BrianMc1958
ID: 16301228
Slight typo in original post (missing close quote):

use MyDB
go
EXEC sp_changedbowner 'Bob';
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16301351
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16301389
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:BrianMc1958
ID: 16301393
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16301405
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
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1000 total points
ID: 16301424
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
 

Author Comment

by:BrianMc1958
ID: 16302015
Please save me from real pain!  I hate real pain!  Give me a magic pill !

--BrianMc1958
0
 

Author Comment

by:BrianMc1958
ID: 16302047
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question