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 (https://blog.sqlauthority.com)
129 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.
Thanks for letting me know that solution worked for you @Mrugank
Thank you!! It worked
Thanks @Angela
You really are awesome… It worked like a charm.
Thanks!
thanks @Enrique
Thanks man it’s work for me..
Thanks for letting me know HK.
Thanks it works
Glad to see this comment Janson.
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.
Based on the script I provided it won’t.
Thanks , It Worked!
your welcome pinkiesnaps.
it is for single databases but i have multiple databases so is there any script to change it in all databases?
Thanks Pinal. It has saved me a lot of pain. Many Thanks for your Blog.
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!
this works fine for any db user in any database wherever..
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
Thank you Pinal! It worked.
Thanks Pinal, it worked like a charm.
Thanks, Pina!
One big caveat is the following from the Books Online, this burned me the first time I had to do this.
When ownership is transferred, permissions on schema-contained objects that do not have explicit owners will be dropped. Cannot change the owner of sys, dbo, or information_schema.
Thank you so much!!! It worked like a charm!!!!
Hello Pinal
I appreciate your help, I was able to solve my problem ñ_ñ
Greetings from El Salvador
Very helpful – thank you!
In my case, I am attempting to delete the user; ‘MyDbUser’. But the script below does not return any results:
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID(‘MyDbUser’);
Oh, what to do?? Christmas is coming!
Thanks, Jake