Drop Database User who Owns Schema

Scenario :

UserName : GPxTestUser is owner of db_datawriter schema, and we wanted to drop the user

Once u made a user as schema owner (this is a common problem when we create Database user using SSMO, because by mistake everybody check the schema instead of Database Role) , then there is no way to revert back or change the owner of schema from "Database User" window. This can confuse a newbie. Though, I feel it should not be allowed to change the Owner of Schema from Database User Window and it should be done from "Schema Property Window" only. What i would say is this option of choosing schema owner should be removed form "Database User" window altogether to avoid confusion.

When we try to drop a database user who owns some schema from Database the system will throw the following Error :-

Msg 15138, Level 16, State 1, Line 1
The database principal owns a schema in the database, and cannot be dropped.

First you should see the Schemas and the curresponding owners

SELECT s.name SchemaName, d.name SchemaOwnerName FROM sys.schemas s INNER JOIN sys.database_principals d ON s.principal_id= d.principal_id


Drop USER gpxtestuser

Msg 15138, Level 16, State 1, Line 1
The database principal owns a schema in the database, and cannot be dropped.

Resolution :-

Alter the Schema Owner to any other Prinicpal; here I am changin owner of DB_Datawriter as DB_Datawriter (earlier it was GPxTestUser)

ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [db_datawriter]

Then the user can be droped

To Transfer/Change the ownership of a object to another Schema.

If you want to drop a Schema , first you should transfer the object owned by this schema to another schema otherwise, when you drop schema you will get a messge as mentioned below :-

Msg 3729, Level 16, State 1, Line 1
Cannot drop schema 'SCH1 ' because it is being referenced by object 'TAB1 '.

Now, before droping the schema SCH1 , the obeject(s) need to be transfered to another schema.

Syntax : ALTER SCHEMA Target_schema_name TRANSFER object_name

The below statement will transfer the Ownership of TAB1 from SCH1 to DBO

ALTER SCHEMA dbo TRANSFER SCH1.TAB1

in the same manner , transfer all the objects to whichever schema you want and then drop the schema.