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.
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.
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'