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
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.
Thank you very much!
Excellent work mate, thanks :)
Excellent! Thanks
That solved my problem. Thank you very much.
k fine then how to remove the schema
thank u
Sorry that’s one-by-one, I recently had to remove a user owning 10+ schemas, based upon information above, this query helped me.
SELECT [Owned Schema] = s.name,
[ALTER_SQL] = ‘ALTER AUTHORIZATION ON SCHEMA::’ + s.name + ‘ TO dbo
GO’
FROM sys.schemas s
WHERE s.principal_id = USER_ID(‘mm2_sk’) — Username here.
GO
Very usefull solution… thanks
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)
Thanks a lot. we are resolved the problem.
incredible… it works!!!!!!!!!!
tks a lot
it works for me!!!!!! you are the best!!!!!!
God bless you Pinal, it worked !!!
Thanks a Lot :))
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?
So much time gained by using the solution you provided. Many thanks thanks for that, Pinal !
Recover sql server databases thanks to programmed method I would recommend you next utility sql fix
Thank you!
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;
Thank you!
Nice solution, I find this quicker than using SSMS GUI.
Thanks for this post :)
it helped me to delete one user and re create it
This was great. I was owning db_owner schema and I couldn’t detach it from my user. Thanks for the script buddy.
Fantastic ! Thank You !
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.
Works for Me