June 30, 2005 at 12:19 pm
I've seen lots of threads with folks who have run into the error ... "The proposed new database owner is already a user in the database." ... when using sp_changedbowner to change the ownership to sa. The proposed solutions work when dealing with sa (change ownership to a user without access to the db, then change it to sa), but I can't manage to change the ownership from sa to another user. I would try dropping the user from the database, but it owns objects. Any ideas out there?
June 30, 2005 at 12:37 pm
First off I would ask why it is important that a particular user owns the database? Usually it's the case that you don't want a particular user to own the database for security reasons. Generally I have found that having sa own the databases makes sense.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 30, 2005 at 12:45 pm
The only reason I found to have a certain person own the database is if that person would be creating objects and you could NOT get them to remember to prefix them with dbo.
Tim S
June 30, 2005 at 12:49 pm
it's sa now for exactly those reasons. but i'm told that the vendor is insisting that the dbo be this other generic account. however, all of the objects appear to be owned by this account already. for that alone, it doesn't make sense to me why the dbo would have to change ... but i wanted to do it just so they could remove it from their troubleshooting efforts.
June 30, 2005 at 1:50 pm
Lookup sp_changedbowner in BOL
June 30, 2005 at 2:35 pm
Are they really saying that the user needs to own the database or are they saying that the user needs to be dbo, or are they saying that the user needs to be in the db_dbowner group? Those are three very different situations.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
July 1, 2005 at 1:51 am
what i did was like this:
1. go to sysusers and look for the uid that i want
2. update sysobjects with the uid that i just found for those objects that i want to change owner
does this help you?
Leo
July 1, 2005 at 9:10 am
We have run across this a couple of times. Strange as it may seem, the vendor is adamant about having their login be the database owner, even though it's not what we would consider "best practice". Here's what you do:
1. Create a dummy account (JunkMan, or some such). and grant it dbo to the database in question.
2. Create a cursor and execute sp_changeobjectowner to your dummy account for each object in the database that is owned by your vendor-specified login (see BOL for exact syntax).
3. Revoke database permissions from the vendor-specified login
4. Execute sp_changedbowner to the vendor-specified login
5. Create a cursor and execute sp_changeobjectowner from your dummy account back to the vendor-specified account.
6. Drop the dummy account.
This will accomplish what you're after.
July 1, 2005 at 9:35 am
In cases like this I love to tell the vendor exactly where they can shove it! For crying out loud, who is the customer here? Vendors either work within my standards or they loose a customer.
Frankly this is one of the major mistakes I see DBA's make. And yes, I do consider it the mistake of the DBA. First off you ought to gain enough respect through technical competence to be listened to and then you should clearly explain why these types of demands by vendors are nonsense or in some cases pose serious security/data integrity threats. I don't make it on many of our proposed vendor's Christmas lists because I force them to prove that they either know what they are doing or that they don't. The majority don't.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
July 1, 2005 at 10:12 am
While in principle I agree with your attitude on this, I've seen several people tank their employment on just such hard-line stands as this.
The cold reality is that in some cases (not all thankfully), the business user buys a product or a business analyst recommends a product without consulting the DBA. Or, the DBA is asked his opinion so late in the evaluation process as to be useless, and is then viewed as "negative" or "a roadblock".
Is this a mistake by the DBA? No. Is this an opportunity to educate the business user who bought the product or the business analyst who recommended the product? Absolutely. In fact, if the DBA fails to do the educating then THAT would be a mistake...a huge one.
(I'll get off my soap-box now...sorry).
July 5, 2005 at 11:49 am
Thanks very much folks for your responses. I did argue the case and it wasn't at all difficult to convince them in this particular case. They found the problem elsewhere. I'll make note of the technical suggestions and give them a try for future reference. Thanks again.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply