/*
Author: Tim Ford
Original link: http://sqlmag.com/security/identifying-sql-server-logins-without-default-database-users
*/
--============================================================
--IDENTIFY ALL LOGINS WITH NO ASSOCIATED DEFAULT DATABASE USER
--============================================================
SET NOCOUNT ON;

DECLARE @sql_text nvarchar(max);
DECLARE @database_name sysname;

IF EXISTS(SELECT name FROM tempdb.sys.tables WHERE name LIKE '#default_db_users_table%')
        BEGIN
                DROP TABLE #default_db_users_table;
        END

CREATE TABLE #default_db_users_table 
        (
                server_login_name sysname NOT NULL
                , database_user_name sysname NOT NULL
                , default_database_name sysname NOT NULL
        );      

DECLARE cur_default_databases CURSOR FORWARD_ONLY STATIC FOR --FORWARD_ONLY STATIC CURSOR FOR
        SELECT name 
        FROM sys.databases 
        ORDER BY name

OPEN cur_default_databases

FETCH NEXT FROM cur_default_databases INTO @database_name

WHILE @@FETCH_STATUS = 0
        BEGIN

                SELECT @sql_text = 
                'INSERT INTO #default_db_users_table(server_login_name, database_user_name, default_database_name)
                SELECT SP.name AS [server_login_name], DP.name AS [database_user_name], SP.default_database_name
                FROM sys.server_principals AS SP
                        INNER JOIN ' + @database_name + '.sys.database_principals AS DP
                                ON SP.sid = DP.sid
                WHERE SP.default_database_name = ''' + @database_name + ''';'

                EXEC sys.sp_sqlexec @sql_text;

                FETCH NEXT FROM cur_default_databases INTO @database_name
        END 

CLOSE cur_default_databases;
DEALLOCATE cur_default_databases;

--===================================================
-- LISTING OF LOGINS MISSING USER IN DEFAULT DATABASE
--===================================================
SELECT SP.name AS login_name
        , SP.default_database_name
        , 'USE [' + SP.default_database_name + ']; CREATE USER [' + SP.name + '] FOR LOGIN [' + SP.name + '];' AS user_create_stmt
FROM sys.server_principals AS SP
        LEFT JOIN #default_db_users_table AS DDUT
                ON SP.name = DDUT.server_login_name
WHERE SP.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN')
        AND SP.name NOT LIKE 'NT %'
        AND SP.name NOT LIKE '##%'
        AND SP.default_database_name IS NOT NULL
        AND DDUT.server_login_name IS NULL
ORDER BY SP.name;


--===================================================
-- CLEANUP
--===================================================
IF EXISTS(SELECT name FROM tempdb.sys.tables WHERE name LIKE '#missing_default_db_users_table%')
        BEGIN
                DROP TABLE #missing_default_db_users_table;
        END;

Add Identify All Logins With No Associated Default Database User script
Add Identify All Logins With No Associated Default Database User script
