SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

dand
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?

Thanks,
--
Dan D.

jen
Flowing Fount of Yak Knowledge

Sweden
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

dand
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?

Thanks,

Dan
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
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 so.name AS objectname, su.name AS ownername
FROM 
	sysobjects so
	INNER JOIN sysusers su ON so.uid = su.uid
WHERE su.name = 'whatevertheusernameis'



MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.11 seconds. Powered By: Snitz Forums 2000