Monday, October 20, 2014

The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

Today when I was removing (deleting) some of the users from one of the SQL Server Database , got below error

The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

Fig 1: Drop User from SQL Server Database Error

From the error message we can clearly tell that the user is the owner of schema. To take a detail look double click on the User in the database as shown below and see which schema is owned by this user.

Fig 2: Check The Owner Of Schema In SQL Server Database.

As we can see that mytestschema is the schema that is owned by user Aamir. To drop user 'Aamir' , we need to change the ownership of mytestschema to some other user. We can transfer the ownership to dbo. If you try to un-check the box for mytestschema , it will not work. 

use this script to change the ownership of schema.

ALTER AUTHORIZATION ON SCHEMA::SchemaName TO dbo 


For our case, it will be 
use TestDB
go
ALTER AUTHORIZATION ON SCHEMA::mytestschema TO dbo

To drop the user , you can right click on user and Delete it or you can use below script to drop it.

USE DBName
go
Drop user UserName