How to drop database user that owns a schema – SQL Server Error: 15138
The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)
To change the schema owner from Sql Server Management Studio:
Expand your database -> Security -> Schemas.
In the Object Explorer Details you can see a list of the schemas and the owners:
Right click on the schema that is owned by the user you want to delete and change the owner (Properties -> General -> Schema Owner).
Query:
Run the following query if the user you are trying to drop is named for example ‘my_app_user’ and it exists in the database ‘AW2008’:
USE AW2008;
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('my_app_user');
Here is my result:
And change the owner of the schema to some other user (for example dbo):
ALTER AUTHORIZATION ON SCHEMA::db_datawriter TO dbo;
When the user you want to delete has no schemas owned you can delete it.
No comments:
Post a Comment