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

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 how to remove login that was dbo

Author  Topic 

dand

13 Posts

Posted - 2005-09-21 : 17:08:38
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
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-22 : 03:28:08
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

13 Posts

Posted - 2005-09-22 : 08:11:23
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

4184 Posts

Posted - 2005-09-22 : 23:38:11
[code]
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'

[/code]

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page
   

- Advertisement -