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’.

Solarwinds

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.

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

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 (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQL SERVER – Merry Christmas and Happy Holidays – Database Properties – Number of Users
Next Post
SQL SERVER – Target Recovery Time of a Database – Advance Option in SQL Server 2012

Related Posts

118 Comments. Leave new

  • Thanks Pinal this has helped me a lot. I was not able to delete the user due to this error and due to production server i was not allowed to restart the SQL services.

    Reply
  • Angela Saayman
    November 7, 2016 2:38 pm

    Thank you!! It worked

    Reply
  • You really are awesome… It worked like a charm.
    Thanks!

    Reply
  • Thanks man it’s work for me..

    Reply
  • Thanks it works

    Reply
  • Thanks Pinal for your blogs. For me the error is ‘Database principal owns a service…’ This is after I copied a database for another customer. I would imagine same work around will work for my error too. But my question is will this work around will have an effect on the original database that I copied from.

    Reply
  • Thanks , It Worked!

    Reply
  • it is for single databases but i have multiple databases so is there any script to change it in all databases?

    Reply
  • azim.lanewala@gmail.com
    July 10, 2017 10:20 pm

    Thanks Pinal. It has saved me a lot of pain. Many Thanks for your Blog.

    Reply
  • Charlie Arehart
    February 3, 2018 1:54 am

    Pinal, I know this is a very old post, but in case otehrs may come across it (as I did today), I wonder: why did you do the “ALTER AUTHORIZATION ON SCHEMA::db_denydatareader TO dbo”? When I create a new DB, I see that the default is that the schema owner is the schema name, so it would seem this should have been “ALTER AUTHORIZATION ON SCHEMA::db_denydatareader TO db_denydatareader”. Would you agree?

    For any who want to see what I mean, go to a given database, then security, then schemas, then right-click and choose properties. That page shows the current owner (and BTW, someone could just make the desired change of schema owner there). But my point is that if you look at a new DB, you might see (as I did) that the schema owner is indeed the name of the schema. (Of course, if it’s been modified by someone it will differ.)

    Not a challenge, Pinal. Just a question. Thanks for all you do!

    Reply
  • this works fine for any db user in any database wherever..

    Reply
  • hello pinal, i am facing issue in report server from last few days, actually i created one package and i deployed it into sql server then i created job for it, i called that job in ssrs report, report is working fine while running normally, but where the issue is after deploying the report in to report server and corporate web portal there i am getting error like this(“An error has occurred during report processing. (rsProcessingAborted)
    Query execution failed for dataset ‘Spjob’. (rsErrorExecutingCommand)
    Cannot execute as the server principal because the principal “sa2″ does not exist, this type of principal cannot be impersonated, or you do not have permission.”)
    i know this is permission error, but i checked everything in sql server, but i didn’t find any solution please help for this issue

    Reply

Leave a Reply

Menu