SQL SERVER: Create AppUser and AppUserReadOnly roles, and map logins to roles

CreateAppRoles.sql

DECLARE @DatabaseName NVARCHAR(128)
DECLARE @SQL NVARCHAR(MAX)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE database_id > 4  -- Excludes system databases (master, tempdb, model, msdb)
    AND state_desc = 'ONLINE'
    AND [name] NOT IN ('DBATools')
    AND is_read_only = 0

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        PRINT '--- Processing database: ' + @DatabaseName + ' ---'
        
        SET @SQL = N'USE [' + @DatabaseName + N']

DROP ROLE IF EXISTS [AppUser]
DROP ROLE IF EXISTS [AppUserReadOnly]
CREATE ROLE [AppUser]
CREATE ROLE [AppUserReadOnly]
DECLARE @sql NVARCHAR(MAX) = ''''
SELECT @sql += CONCAT(''
GRANT EXECUTE, INSERT, UPDATE, DELETE, SELECT ON SCHEMA::'', QUOTENAME([s].[name]), '' TO [AppUser]
GRANT EXECUTE, SELECT ON SCHEMA::'', QUOTENAME([s].[name]), '' TO [AppUserReadOnly]
'')
FROM sys.[schemas] AS [s]
WHERE s.[schema_id] = 1
    OR (s.[schema_id] > 4 AND s.[schema_id] < 16384)
PRINT @sql
EXEC sp_executesql @sql'

        EXEC sp_executesql @SQL
        
        PRINT '--- Completed: ' + @DatabaseName + ' ---'
        PRINT ''
    END TRY
    BEGIN CATCH
        PRINT 'ERROR in database ' + @DatabaseName + ': ' + ERROR_MESSAGE()
        PRINT ''
    END CATCH

    FETCH NEXT FROM db_cursor INTO @DatabaseName
END

CLOSE db_cursor
DEALLOCATE db_cursor

PRINT '=== All databases processed ==='

MapLoginsToAppUserRole.sql

DECLARE @LoginName NVARCHAR(128) = 'YourLoginName'  -- Change this to your login name
DECLARE @RoleName NVARCHAR(128) = 'AppUser'  -- Change to 'AppUser' or 'AppUserReadOnly'
DECLARE @DatabaseName NVARCHAR(128)
DECLARE @SQL NVARCHAR(MAX)

-- Validate that the login exists
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = @LoginName)
BEGIN
    PRINT 'ERROR: Login [' + @LoginName + '] does not exist on this server'
    RETURN
END

DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE database_id > 4  -- Excludes system databases (master, tempdb, model, msdb)
    AND state_desc = 'ONLINE'
    AND is_read_only = 0

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        PRINT '--- Processing database: ' + @DatabaseName + ' ---'
        
        SET @SQL = N'USE [' + @DatabaseName + N']

-- Create user if it doesn''t exist
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ''' + @LoginName + ''' AND type IN (''S'', ''U''))
BEGIN
    CREATE USER [' + @LoginName + '] FOR LOGIN [' + @LoginName + ']
    PRINT ''Created user [' + @LoginName + '] in database [' + @DatabaseName + ']''
END
ELSE
BEGIN
    PRINT ''User [' + @LoginName + '] already exists in database [' + @DatabaseName + ']''
END

-- Add user to role
ALTER ROLE [' + @RoleName + '] ADD MEMBER [' + @LoginName + ']
PRINT ''Added [' + @LoginName + '] to role [' + @RoleName + '] in database [' + @DatabaseName + ']'''

        EXEC sp_executesql @SQL
        
        PRINT '--- Completed: ' + @DatabaseName + ' ---'
        PRINT ''
    END TRY
    BEGIN CATCH
        PRINT 'ERROR in database ' + @DatabaseName + ': ' + ERROR_MESSAGE()
        PRINT ''
    END CATCH

    FETCH NEXT FROM db_cursor INTO @DatabaseName
END

CLOSE db_cursor
DEALLOCATE db_cursor

PRINT '=== All databases processed ==='