Last day I had faced issue on SQL Server Login. I was
trying to remove the login from database but every single time I was getting
error and was not able to remove the user.
How to resolve following error.
The database principal owns a schema in
the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)
I was searching on google and find the solution. 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 i was trying to delete user
which is named as ‘tester’ and it exists in the database ‘Testdb’.
Now run following script with the context of the
database where user belongs.
USE Testdb;
SELECT s.name
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('tester');
In my query I get following two schema as a result.
two schema name "tqd" and "cls"
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::cls TO dbo;
ALTER AUTHORIZATION ON SCHEMA::tqd 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::YourSchemaNa TO dbo;
Thanks to pinal dave for such a good article.
Regards
R.Karthikeyan
