The database principal owns a schema in the database and cannot be dropped.

The error comes when we try to drop user from the database and you are not able to drop it.
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.

The database principal owns a schema in the database, and cannot be dropped.

Resolution / Fix:
Let us assume that user was trying to delete user which is named as ‘MyDBUser’ and it exists in the database ‘MyDatabase’.
Now run following script with the context of the database where user belongs.

USE MyDatabase;
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('MyDBUser');

In my query I get following two schemas as a result.

1    db_denydatareader
2    db_owner

Now let us run following query. In our case we have two schemas so we will execute it two times.

ALTER AUTHORIZATION ON SCHEMA::db_denydatareader TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo;

Now issue would have been fixed and you can drop user by following query

EXEC sp_dropuser 'MyDBUser'

 


<<Click here to see all posts>>

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.