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
Hi,
I did the same but I have one schema which I am not able to alter and getting below error message:
“Cannot find the schema ‘$ndo$navlistner’, because it does not exist or you do not have permission.”
Can you help..
Thanks in advance.
Regards
Ratnesh
As the error message says, either you don’t have permissions or name is incorrect.
Are you able to see that schema in sys.schemas?
Thank you so much. You saved me a ton of time when I was working on a Saturday.
Brian, I am glad after hearing that.
Hi Pinal,
Does not work because the user actually doesn’t own any schemas.
When I try to run this query SELECT s.name FROM sys.schemas s WHERE s.principal_id = USER_ID(‘byname’); and got back a result set of 0 rows.
Yet the error persists.
How can this be resolved?
Hello Luca, Did you find the way to fix it? I have the same problem and cannot fix it.
SQL doesn’t let me delete the user but the select does not return any schema.
Thank you,
Are you sure that you are running it in right database?
Thanks! I had attached a database from my old machine and couldn’t fix this the normal way through Management Studio. This did the trick!
Bill – Thanks for your comment and letting me know.
Thanks!! it was really helpfull
Excellent! You’re the best. Thanks
Working 100% and you are the excellent ..
How can I execute the below sql
my account hold the schema:NT SERVICE\ReportServer
ALTER AUTHORIZATION ON SCHEMA::NT SERVICE\ReportServer TO dbo
schema name has space “NT SERVICE\ReportServer”, so alter is not working…Please help
Two questions: 1) I am confused as to how db_owner, db_accessadmin, etc (the default database roles) have schemas with the exact same names. What is the functional benefit? And 2) by default all of these db_% schemas are have authorizations which match the name of the schema. For example, you could run something like the following to get everything back to the default, “ALTER AUTHORIZATION ON SCHEMA::db_accessadmin TO db_accessadmin;”. Why do you suggest using, “ALTER AUTHORIZATION ON SCHEMA::db_accessadmin TO dbo”;?
Thanks much ! You saved my day !
Hi, I can not restore the backup to remove the ‘execute’ issue. Is there anyway to restore the database? thank you
you need to alter authorization.
thanks a lot, that worked for me
thanks for the confirmation quaidox!
Thanks a lot
Thanks a Lot, this helped me a lot,
Thank you for this, this also resolved my issue.
you are awesome boss… saved me a lot of time today!!!
@PinalDave Sir, it helped me today… you are doing a social work… Thanks a ton.
Thank you Ritesh! You are very kind!
Fantastic Job!!!
Thanks @Sivasubramaniam