SQL: Generate Login and database mapping for login

GenerateLoginAndDatabaseMapping.sql

DECLARE @LoginName NVARCHAR(128) = 'loginname';  -- Change this
DROP TABLE IF EXISTS #ScriptOutput;

CREATE TABLE #ScriptOutput (
    SortOrder INT IDENTITY(1,1),
    ScriptLine NVARCHAR(MAX)
);

-- =============================================
-- Part 1: Script the LOGIN creation
-- =============================================
INSERT INTO #ScriptOutput (ScriptLine)
SELECT '-- ============================================='
UNION ALL SELECT '-- LOGIN CREATION'
UNION ALL SELECT '-- =============================================';

INSERT INTO #ScriptOutput (ScriptLine)
SELECT 
    CASE 
        WHEN sp.type = 'S' THEN 
            'CREATE LOGIN ' + QUOTENAME(sp.name) + ' WITH PASSWORD = ''<password_here>'', ' +
            'DEFAULT_DATABASE = ' + QUOTENAME(sp.default_database_name) + ', ' +
            'DEFAULT_LANGUAGE = ' + QUOTENAME(sp.default_language_name) + ', ' +
            'CHECK_EXPIRATION = ' + CASE WHEN sl.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', ' +
            'CHECK_POLICY = ' + CASE WHEN sl.is_policy_checked = 1 THEN 'ON' ELSE 'OFF' END + ';'
        WHEN sp.type IN ('U', 'G') THEN 
            'CREATE LOGIN ' + QUOTENAME(sp.name) + ' FROM WINDOWS WITH ' +
            'DEFAULT_DATABASE = ' + QUOTENAME(sp.default_database_name) + ', ' +
            'DEFAULT_LANGUAGE = ' + QUOTENAME(sp.default_language_name) + ';'
    END
FROM sys.server_principals sp
LEFT JOIN sys.sql_logins sl ON sp.principal_id = sl.principal_id
WHERE sp.name = @LoginName;

-- Disable if needed
INSERT INTO #ScriptOutput (ScriptLine)
SELECT 'ALTER LOGIN ' + QUOTENAME(sp.name) + ' DISABLE;'
FROM sys.server_principals sp
WHERE sp.name = @LoginName AND sp.is_disabled = 1;

-- Server role memberships
INSERT INTO #ScriptOutput (ScriptLine)
SELECT 'ALTER SERVER ROLE ' + QUOTENAME(sr.name) + ' ADD MEMBER ' + QUOTENAME(@LoginName) + ';'
FROM sys.server_role_members srm
JOIN sys.server_principals sr ON srm.role_principal_id = sr.principal_id
JOIN sys.server_principals sp ON srm.member_principal_id = sp.principal_id
WHERE sp.name = @LoginName;

-- =============================================
-- Part 2: Database users and roles via sp_MSforeachdb
-- =============================================
INSERT INTO #ScriptOutput (ScriptLine)
SELECT ''
UNION ALL SELECT '-- ============================================='
UNION ALL SELECT '-- DATABASE USER MAPPINGS & ROLES'
UNION ALL SELECT '-- =============================================';

DECLARE @DBName NVARCHAR(128);
DECLARE @SQL NVARCHAR(MAX);

DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases WHERE state = 0 AND database_id > 4;

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DBName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = N'
    USE ' + QUOTENAME(@DBName) + N';
    
    -- Check if user exists in this database
    IF EXISTS (
        SELECT 1 FROM sys.database_principals dp
        JOIN sys.server_principals sp ON dp.sid = sp.sid
        WHERE sp.name = @Login
    )
    BEGIN
        INSERT INTO #ScriptOutput (ScriptLine) VALUES ('''');
        INSERT INTO #ScriptOutput (ScriptLine) VALUES (''-- Database: ' + @DBName + N''');
        INSERT INTO #ScriptOutput (ScriptLine) VALUES (''USE ' + QUOTENAME(@DBName) + N';'');
        
        -- Create user statement
        INSERT INTO #ScriptOutput (ScriptLine)
        SELECT 
            ''CREATE USER '' + QUOTENAME(dp.name) + '' FOR LOGIN '' + QUOTENAME(sp.name) + 
            CASE WHEN dp.default_schema_name IS NOT NULL 
                 THEN '' WITH DEFAULT_SCHEMA = '' + QUOTENAME(dp.default_schema_name) 
                 ELSE '''' END + '';''
        FROM sys.database_principals dp
        JOIN sys.server_principals sp ON dp.sid = sp.sid
        WHERE sp.name = @Login;
        
        -- Role memberships
        INSERT INTO #ScriptOutput (ScriptLine)
        SELECT ''ALTER ROLE '' + QUOTENAME(r.name) + '' ADD MEMBER '' + QUOTENAME(dp.name) + '';''
        FROM sys.database_role_members drm
        JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
        JOIN sys.database_principals dp ON drm.member_principal_id = dp.principal_id
        JOIN sys.server_principals sp ON dp.sid = sp.sid
        WHERE sp.name = @Login;
        
        -- Explicit permissions
        INSERT INTO #ScriptOutput (ScriptLine)
        SELECT 
            CASE perm.state WHEN ''G'' THEN ''GRANT'' WHEN ''W'' THEN ''GRANT'' WHEN ''D'' THEN ''DENY'' END 
            + '' '' + perm.permission_name 
            + CASE 
                WHEN perm.class = 1 THEN '' ON '' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + ''.'' + QUOTENAME(obj.name) 
                WHEN perm.class = 3 THEN '' ON SCHEMA::'' + QUOTENAME(SCHEMA_NAME(perm.major_id))
                ELSE '''' 
              END
            + '' TO '' + QUOTENAME(dp.name) 
            + CASE WHEN perm.state = ''W'' THEN '' WITH GRANT OPTION'' ELSE '''' END + '';''
        FROM sys.database_permissions perm
        JOIN sys.database_principals dp ON perm.grantee_principal_id = dp.principal_id
        JOIN sys.server_principals sp ON dp.sid = sp.sid
        LEFT JOIN sys.objects obj ON perm.major_id = obj.object_id AND perm.class = 1
        WHERE sp.name = @Login
          AND perm.class IN (0, 1, 3);
    END';
    
    EXEC sp_executesql @SQL, N'@Login NVARCHAR(128)', @Login = @LoginName;
    
    FETCH NEXT FROM db_cursor INTO @DBName;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;

-- =============================================
-- Output the complete script
-- =============================================
SELECT ScriptLine FROM #ScriptOutput ORDER BY SortOrder;

DROP TABLE IF EXISTS #ScriptOutput;