delete db_owner problem, change db_owner to another login, dbo problem

[katekis]

I am trying to delete a user from a database.

I am using SQL Server 2005

The user I am trying to delete was the user we used when created the database. Aside of being the owner on the database this database user is no longer needed. I want to change ownership of the database to another user and delete the old db_Owner.

[oldlogin] - the user I want to delete
[goodlogin] - the login I want as DB Owner

I can not tell why it won't let me make any changes to the [oldlogin]

In Management Studio--> Security --> User Maping
I try to uncheck the map box on the database where I want to removed [oldlogin] and I get "Cannot drop the user 'dbo'

I updated [goodlogin] to be db_owner on the database.

When I look at [goodlogin] in SQL Server Security they are listed db_owner but under 'User' it says 'dbo' instead of their login name?
I can not update this to be anything but 'dbo'.

When I try to update [oldlogin] in SQL Server Security -- user maping -- Database role membership for database, and uncheck db_owner I get error "Can not use the speical principle 'dbo'."

When I look at [goodlogin] under the Database Security I do not see it. When I try to add it it says "The login already has an account under a different user name"

Any help is greatly appreciated to fix this database ownership problem.

 

[SQLBill]

db_owner is just a role that gives a login the same permissions as if they owned the database.

DBO is the schema for database owner. Any login that has the default of DBO can access/create objects owned by DBO. This is very common. Otherwise, users could only create objects with their own login (goodlogin.tablename) and then they would have to grant access to every other login that needed to see that table.

You should be able to drop the oldlogin. It may have a schema associated with it (non-dbo) if so, first check to see what that schema owns, change the owner to another schema and then drop the schema/user. For example, to drop oldlogin, you would have to make sure there are no tables, functions, stored procedures or anything else owned by them (for example: oldlogin.tablename).

Goodlogin might be aliased. For example, you could have myDomain\Goodlogin as a Server Login. But in the database it might just be Goodlogin.

-SQLBill



The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875