Friday, January 2, 2015

How to fix “The database principal owns a schema in the database, and cannot be dropped.”

PS C:\Users\Administrator> sqlcmd -S  .
1> use DB1
2> go
Changed database context to 'DB1'.

1> drop user U1
2> go
Msg 15138, Level 16, State 1, Server WIN-922S55M9QDP, Line 1
The database principal owns a schema in the database, and cannot be dropped.

1> select  name from sys.schemas where principal_id=DATABASE_PRINCIPAL_ID('U1')
2> go
name
----------------------------------------------------
db_ddladmin
db_datareader
db_datawriter

(3 rows affected)


1> alter authorization on schema::db_ddladmin to dbo
2> go

1> alter authorization on schema::db_datareader to dbo
2> go

1> alter authorization on schema::db_datawriter to dbo
2> go

1> select  name from sys.schemas where principal_id=DATABASE_PRINCIPAL_ID('U1')
2> go
name
----------------------------------------------------

(0 rows affected)
1> drop user u1
2> go