Droping a login marked as db_owner | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Droping a login marked as db_owner

My manager created a login for himself on our new sql2005 server, he marked it as "db_owner". I want to delete this but i get the msg that the login owns an object (something msg like that)… is there anyway to delete/drop a user or a login that’s marked as "db_owner"?
first remove object owner ship by, using sp_changeobjectowner —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

True, as long as it is associated with any object in database it is not possible to drop it. Be aware this is provided for backward compatibility and you must use SCHEMA method or use ALTER AUTHORISATION in future. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
so I have to find out what that login owns and then change the object ownership? if so , anyway to find out what that login is owning?
You can query sys.objects to find anything that’s owned by your login like this select * from sys.objects
where schema_id = schema_id(‘loginname’)
I ran that select from sysobjects.. I got no rows back. I double clicked on the user and db_owner was not checked. There’s no Login for it . I want to delete it from "users"… I get this error when trying to delete it:
Drop failed for User ‘<username’>ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) —————————— The database principal has granted or denied permissions to objects in the database and cannot be dropped. (Microsoft SQL Server, Error: 15284)
?? how can i delete this??

I did this:
select * from sys.database_permissions where grantor_principal_id = user_id (‘user to be deleted’); Igot 4 rows back with these columns as part of the rows:
ALTER
CONTROL
IMPERSONATE
VIEW DEFINITION Then I did this: REVOKE VIEW DEFINITION FROM [My User] CASCADE REVOKE ALTER ON USER::[My User] TO public Did that for all rows, ran the first select, but still getting the same 4 rows and cant delete
]]>