I am trying to drop one of the role in my SQL Server database. I dropped all the members from the role and when i tried to drop role i got this error message:
Msg 15138, Level 16, State 1, L开发者_运维技巧ine 13
The database principal owns a schema in the database, and cannot be dropped.
Does anyone know why? I checked the Owned Schema and it only had check sign in its own name.
You cannot drop a database principal that owns a schema. You have to transfer the schema ownership to some other database principal or drop the schema before you can drop the database principal.
15138 error is due to the user you are trying to delete owns a schema.
If you run the below query you will get the schema owned by the user.
USE DatabaseName;
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('UserName');
Let us say it returns 'db_denydatareader' schema. Then you can assign that schema to default user 'dbo' using the below query.
ALTER AUTHORIZATION ON SCHEMA::db_denydatareader TO dbo;
精彩评论