Fix: Error: 15138 – The database principal owns a schema in the database, and cannot be dropped

Posted on Updated on

Fix: Error: 15138 – The database principal owns a schema in the database, and cannot be dropped

Recently I was dropping orphan user from database SSISTEST and Got below error:

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

The error message clearly tell us that the user is associated with one of the database schema, So to delete this user we have to transfer this schema to another user.

To find which schema is associated with that user use below query:

USE SSISTEST

GO

SELECT s.Name

FROM sys.schemas s

WHERE s.principal_id = USER_ID(‘test’);

This query will give below output:

name
db_ddladmin
db_datareader
db_datawriter

 

Then use the output to assign to other user let’s say dbo.

ALTER AUTHORIZATION ON SCHEMA:: db_ddladmin  TO dbo

GO

ALTER AUTHORIZATION ON SCHEMA:: db_datareader  TO dbo

GO

ALTER AUTHORIZATION ON SCHEMA:: db_datawriter  TO dbo

 

DROP USER test

Generic Script:

SELECT s.name

FROM sys.schemas s

WHERE s.principal_id = USER_ID(‘YourUserID’);

Now replace the result name in following script:

ALTER AUTHORIZATION ON SCHEMA::YourSchemaName TO dbo;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s