Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 how to remove login that was dbo
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

13 Posts

Posted - 09/21/2005 :  17:08:38  Show Profile  Reply with Quote
I saw a login that was in the sysadmin role and shouldn't have been. When I went to the database access tab under properties for the login, each database had "dbo" in the user column. I couldn't change it so I removed the login and added him back. After being sure the login had only db_datareader rights, when I tried to save the new login I got an error "the login already has an account under a different user name". When I clicked ok I got an error "Cannot drop the database owner". I'm in a loop and can't get out of EM.

How do I fix it? Would using sp_changedbowner in QA to change the database owner to another login fix it?

Dan D.

Flowing Fount of Yak Knowledge

4110 Posts

Posted - 09/22/2005 :  03:28:08  Show Profile  Send jen a Yahoo! Message  Reply with Quote
yes, sp_changedbowner will transfer ownership from that account to another you specified

but if the account has created objects and in his ownership you need to issue sp_changeobjectowner too before you can drop his account from that database

keeping it simple...
Go to Top of Page

Starting Member

13 Posts

Posted - 09/22/2005 :  08:11:23  Show Profile  Reply with Quote
I know that user created some of the databases and the tables within the database. Is there a way to find all objects owned by that login?


Go to Top of Page

Pointy Haired Yak DBA

4184 Posts

Posted - 09/22/2005 :  23:38:11  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote

SELECT AS objectname, AS ownername
	sysobjects so
	INNER JOIN sysusers su ON so.uid = su.uid
WHERE = 'whatevertheusernameis'


When life gives you a lemon, fire the DBA.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000