Delete orphaned SQL Users
There seems to be some confusion over terminology. A user exists at the database level and is removed when you drop a database. A login exists at the server level and is mapped to a database user to get access to the database.

I'm assuming you want to find all logins that are not mapped to a database user and are therefore potentially no longer in use. The below query will achieve that:

CREATE TABLE ##UnusedLogins (
    [Login] NVARCHAR(255),
    [InUse] BIT
)

INSERT INTO ##UnusedLogins ([Login], [InUse])
SELECT [login].[name] AS [Login],
    0 AS [InUse]
FROM sys.server_principals [login]
WHERE [login].[name] NOT LIKE 'NT SERVICE\%'
    AND [login].[name] NOT LIKE 'NT AUTHORITY\%'
    AND [login].[name] NOT LIKE '##%'
    AND [login].[type_desc] &lt;&gt; 'SERVER_ROLE'
    AND [login].[name] &lt;&gt; 'sa'

EXEC sp_msforeachdb 'UPDATE ul
SET [InUse] = 1
FROM ##UnusedLogins ul
INNER JOIN sys.server_principals [login] ON [login].[name] = ul.[Login]
INNER JOIN [?].sys.database_principals [user] ON [user].[sid] = [login].[sid]'

SELECT * FROM ##UnusedLogins

DROP TABLE ##UnusedLogins


Any login in the output that has a 1 in the InUse column is mapped to a database user. Any login with a 0 is not mapped. This does not mean the login is not in use, simply that it is not mapped to any database user.