How to identify database users and their owned schemas before dropping server logins?

While deleting logins, there might be a possibility that there are users in databases along with chances that they own schemas in those databases.

Is there a way to find this out with a single select?

This is the warning message I get:


Deleting server logins does not delete the database users associated
with the logins. To complete the process, delete the users in each
database. It may be necessary to first transfer the ownership of
schemas to new users.

The query below will return the schema owner information. You can change the JOIN to sys.database_principals to a LEFT JOIN to also return schema owners that are not logins, such as database roles and users without a login.

SELECT
      sp.name AS OwningLogin
    , dp.name AS OwningUser
    , s.name AS SchemaName
FROM sys.schemas AS s
JOIN sys.database_principals AS dp ON
    dp.principal_id = s.principal_id
JOIN sys.server_principals AS sp ON
    sp.sid = dp.sid;

StackExchange Database Administrators Q#107870, answer score: 2