The database principal owns a database role and cannot be dropped

This morning I was migrating Microsoft SQL databases from an old server to a new server. After migrating the database, I needed to delete an existing login user from the database and add a new one. When I tried to delete the user I got the following error thrown at me.

I opened the Database Roles node under Security and found a long list of roles. Following image shows partial list of those roles.

I remember that user login had db_owner role on old server. So I click on that role and found the user name there. I deleted the user from that role. I tried to delete the user, again the same error. This means we need a way to list all the roles that are owned by this user. Lets see some underlying system tables where this information is available.

sys.database_principals Database Table

You will find all the principals in your server in sys.database_principals table. You can do a quick Select query on this table to see what all is there. Following image shows part of the information from that table.

As you can see it lists all the roles in your server and principals associated with it. The column of interest in this table is owning_principal_id. This is the field that contains the ID of user who owns that schema. So you just need to run a query that will list all the roles in this table that has owning_principal_id same as principal_id and you have all that you need.

select dbp1.name as DbRole, dbp2.name as DbRoleOwner 
from sys.database_principals as dbp1 
inner join sys.database_principals as dbp2 
on dbp1.principal_id = dbp2.owning_principal_id 
where dbp1.name = 'dbuser'

To run this query in your database, replace dbuser with whatever user you are trying to delete. This query will give you all the roles owned by this user. Then you can go to each of those roles in Database Roles node and replace it with some universal role. Replacing it with dbo will be a safe bet. Once you have changed owner of all those roles, you can simply delete that user from security node of that database. After that you can change the role owner to new user if you need to.