Re: Cannot delete SQL user (dbowner)

From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 09/03/03 

Date: Wed, 3 Sep 2003 07:16:00 -0700

Carlton

Putting a user into the db_owner role is does not change the mapping of the
user dbo to the name cwhitmor in the login table. If you look at sp_helpdb
output, it will tell you who is seen as the true owner of the database.

To change the true owner, you must run the procedure sp_changedbowner.

USE mydb
exec sp_changedbowner 'domain\cwhitmor'
USE master
EXEC sp_droplogin cwhitmor 

--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Carlton Whitmore" <cwhitmore@advocacyinc.org> wrote in message
news:0bc801c37220$b55da900$a401280a@phx.gbl...
> Kalen,
> I'm trying to delete a login. We moved this server into
> our AD so now we have a lot of overlap on logins.
> I'd like to delete the duplicates.
> When I try to delete the login this is the error message:
> "Error 15174: Login 'cwhitmor' owns one or more Database
> (s). Change the owner of the following Database(s) before
> dropping login: Databases owned by login: Test1, Test2,
> Test3, Test4"
> I've already assigned the db_owner rights on all of these
> databases to domain\cwhitmore, but I still cannot delete
> cwhitmor.
> So if I run the procedure you mentioned below that will
> take care of this problem?
> thanks for your help,
> Carlton.
>
>
>
> >-----Original Message-----
> >Can you explain a bit more about what you did and what
> you need? What user
> >are you trying to delete? Or are you trying to delete a
> login. Please make
> >sure you understand the difference between logins and
> users; there is a lot
> >of good info in the Books Online.
> >
> >What message do you get when you say you are not allowed
> to delete the user?
> >
> >Every database has a user named dbo and that can never
> change. What can
> >change is the login name that the dbo user is mapped to.
> When you execute
> >the stored procedure sp_changedbowner, you are changing
> the mapping between
> >the user dbo and a login name.
> >
> >--
> >HTH
> >----------------
> >Kalen Delaney
> >SQL Server MVP
> >www.SolidQualityLearning.com
> >
> >
> >"Carlton Whitmore" <cwhitmore@advocacyinc.org> wrote in
> message
> >news:030101c37197$318b29b0$a501280a@phx.gbl...
> >> I've reassigned the owner to another user, but SQL 2000
> >> will not allow me to delete the user. How can I get
> around
> >> this?
> >> Carlton.
> >>
> >
> >
> >.
> >