How can I check if one specific login has any database users mapped on it? I've create some logins and database users. So I would like to accomplish the following.
Drop user from database:
USE [dbname]
GO
IF EXISTS (SELECT 1 FROM sysusers WHERE NAME = 'username')
    DROP USER [username]

Here I would like to check if the login has any database users in other databases and if not drop login:
USE [master]
GO
DROP LOGIN [username]
GO
use this query:
IF OBJECT_ID(N'tempdb..#results', 'U') IS NOT NULL 
    DROP TABLE #results;

CREATE TABLE #results(
    LoginName sysname
    ,LoginType nvarchar(60)
    ,IsMustChange bit
    ,DatabaseName sysname NULL
    ,DatabaseUserName sysname NULL
    ,DatabaseRoleName sysname NULL
    );

EXEC sp_MSforeachdb '
USE [?];
INSERT INTO #results
    SELECT
        sp.name AS LoginName
        ,sp.type_desc AS LoginType
        ,CAST(LOGINPROPERTY ( sp.name , ''IsMustChange'' ) AS bit) AS IsMustChange
        ,DB_NAME() AS DatabaseName
        ,dp.name AS DatabaseUserName
        ,r.name AS DatabaseRoleName
    FROM sys.server_principals sp
    LEFT JOIN sys.database_principals dp ON
        dp.sid = sp.sid
    LEFT JOIN sys.database_role_members drm ON
        drm.member_principal_id = dp.principal_id
    LEFT JOIN sys.database_principals r ON
        r.principal_id = drm.role_principal_id
    WHERE sp.name = ''SomeLogin''';

SELECT * FROM #results;

IF OBJECT_ID(N'tempdb..#results', 'U') IS NOT NULL 
    DROP TABLE #results;