https://ibmimedia.com/blog/56/fix-sql-error-15138
SQL SERVER – Fix: Error: 15138 – The database principal owns a schema in the database, and cannot be dropped
Workaround / Resolution / Fix:
Let us assume that user was trying to delete user which is named as ‘fixadmin’ and it exists in the database ‘fixDatabase’.
Now run following script with the context of the database where user belongs.
USE fixDatabase;
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('fixadmin');
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;
Complete fix here https://ibmimedia.com/blog/56/fix-sql-error-15138
#schema #sql #database
