SQL SERVER: Map login to all user databases except exclude list

MapLoginToDboOnUserDbs.sql

SET NOCOUNT ON;

DECLARE @LoginName sysname = N'login-name';
DECLARE @DryRun    bit     = 1;   -- 1 = print only, 0 = execute

DECLARE @Excluded TABLE (DatabaseName sysname PRIMARY KEY);
INSERT INTO @Excluded (DatabaseName) VALUES
    (N'master'),
    (N'tempdb'),
    (N'model'),
    (N'msdb'),
    (N'ReportServer'),
    (N'ReportServerTempDB'),
    (N'DbaTools');
    -- add any other DBs to skip here

DECLARE @DbName sysname, @Sql nvarchar(max);

SELECT d.name
FROM   sys.databases d
WHERE  d.state_desc   = N'ONLINE'
  AND  d.is_read_only = 0
  AND  d.database_id  > 4                        -- skip system DBs
  AND  d.name NOT IN (SELECT DatabaseName FROM @Excluded)
ORDER BY d.name;

DECLARE db_cur CURSOR LOCAL FAST_FORWARD FOR
SELECT d.name
FROM   sys.databases d
WHERE  d.state_desc   = N'ONLINE'
  AND  d.is_read_only = 0
  AND  d.database_id  > 4                        -- skip system DBs
  AND  d.name NOT IN (SELECT DatabaseName FROM @Excluded)
ORDER BY d.name;

OPEN db_cur;
FETCH NEXT FROM db_cur INTO @DbName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Sql = N'
USE ' + QUOTENAME(@DbName) + N';

IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ' + QUOTENAME(@LoginName, '''') + N')
    CREATE USER ' + QUOTENAME(@LoginName) + N' FOR LOGIN ' + QUOTENAME(@LoginName) + N';

IF NOT EXISTS (
    SELECT 1
    FROM   sys.database_role_members rm
    JOIN   sys.database_principals  r ON r.principal_id = rm.role_principal_id
    JOIN   sys.database_principals  m ON m.principal_id = rm.member_principal_id
    WHERE  r.name = N''db_owner''
      AND  m.name = ' + QUOTENAME(@LoginName, '''') + N'
)
    ALTER ROLE db_owner ADD MEMBER ' + QUOTENAME(@LoginName) + N';
';

    IF @DryRun = 1
        PRINT @Sql;
    ELSE
    BEGIN
        BEGIN TRY
            EXEC sys.sp_executesql @Sql;
            RAISERROR(N'OK  : %s', 0, 1, @DbName) WITH NOWAIT;
        END TRY
        BEGIN CATCH
            DECLARE @msg nvarchar(2048) = N'FAIL: ' + @DbName + N' -- ' + ERROR_MESSAGE();
            RAISERROR(@msg, 0, 1) WITH NOWAIT;
        END CATCH
    END

    FETCH NEXT FROM db_cur INTO @DbName;
END

CLOSE db_cur;
DEALLOCATE db_cur;