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

Last day I had excellent fun asking puzzle on SQL Server Login SQL SERVER – Merry Christmas and Happy Holidays – Database Properties – Number of Users. One of the user sent me email asking urgent question about how to resolve following error. Reader was trying to remove the login from database but every single time he was getting error and was not able to remove the user.

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

As per him it was very urgent and he was not able to solve the same. I totally understand his situation and here is the quick workaround to the issue. The reason for error is quite clear from the error message as there were schema associated with the user and that needs to be transferred to another user.

Workaround / Resolution / Fix:

Let us assume that user was trying to delete user which is named as ‘pinaladmin’ and it exists in the database ‘AdventureWorks’.

Now run following script with the context of the database where user belongs.

USE AdventureWorks;
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('pinaladmin');

In my query I get following two schema as a result.

Now let us run following query where I will take my schema and and alter authorization on schema. In our case we have two schema so we will execute it two times.

ALTER AUTHORIZATION ON SCHEMA::db_denydatareader TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_denydatawriter TO dbo;

Now if you drop the database owner it will not throw any error.

Here is generic script for resolving the error:

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;

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

54 thoughts on “SQL SERVER – Fix: Error: 15138 – The database principal owns a schema in the database, and cannot be dropped

  1. this is Great!! But, we can even do it in simple way Using Management studio.

    go to: Object Explorer> Databases> Adventure works > Security > Schemas
    –> From the list, Find out which Schema’s are owned by the user(we can easily find out from a glance).
    –> Then either change the owner or remove the Schema.
    –> It Is recommended to change system schemas to dbo.
    –> Drop user now.

  2. Hi Dave

    I visited your blog and found it very helpful but didn’t find a place here where i can post a Question. So excuse me for posting it here

    I am in a scenario where you experience will be very handy. I am developer basically and not much into Administration.

    We have a Centralized Database Connected on a Server, Online. Now the problem is our users work on remote location where no internet is available. Since we cannot stop them from entering data we have provided them offline database. Every user has its own database on its Laptop

    Apple of Discord is writing back that offline data to the server and how do we ensure that one user will not overwrite the data of other user?

    is there a smooth Synchronization solution available? a Tool or something that can be helpful in my scenario..

    Your response will very much be appreciated

    Abdul Rehman
    (Bahrain)

  3. Does not work because the user actually doesn’t own any schemas Ran this query SELECT name FROM sys.schemas WHERE principal_id = USER_ID(‘myuser’) and got back a result set of 0 rows. Yet the error persists. How can this be resolved?

  4. I think it will be goo to re-assign the schema where it originally belong: ALTER AUTHORIZATION ON SCHEMA::db_denydatareader TO db_denydatareader;
    and ALTER AUTHORIZATION ON SCHEMA::db_denydatawriter TO db_denydatawriter;

  5. Where is the like button!? Every time I have a SQL problem and the search returns your page as one of the possible solutions, it puts a smile on my face, because I know the problem is about to be fixed.

  6. Thanks, Dave – this was exactly what I was looking for. And I like the scripting method because it gives me a better understanding of SQLServer internals.

  7. Hi Dave,
    How to restrict the users to access the objects of different schema under same Database without schema name.

    Ex if I have two users dbo. and Test respective default schemas are dbo. and TEST_SCHEMA. objects which is present in dbo schema and not in TEST_SCHEMA Now I want TEST USER to allow acces dbo user’s objs with schema Name only if the same objects name is not present under TEST_SCHEMA.

    as Select * from dbo.emp not select * from emp (if emp is not in TEST_SCHEMA)

  8. Hi Dave,

    I have a sql 2008 instance where dbo is orphaned. Found by running sp_change_users_login ‘report’.

    Any ideas how to fix this?

  9. Hello, I changed the scheme but i still get the catalog error.
    TITLE: Microsoft SQL Server Management Studio
    Drop failed for User ‘eCoNoDatUser’. (Microsoft.SqlServer.Smo)
    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

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

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