/*
12.03.2025, TSC
*/
BEGIN TRANSACTION 
SET XACT_ABORT ON;
SET NOCOUNT ON;

DECLARE @loginName NVARCHAR(128) = 'centralinfra\L-CI-AP-SQL-PROD_MED_Team';
DECLARE @sql NVARCHAR(MAX)='';

-- Cursor to iterate through all user databases
DECLARE db_cursor CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
SELECT d.name
FROM sys.databases d
WHERE d.database_id > 4 -- Exclude system databases
AND d.[source_database_id] IS NULL --not a snapshot
AND d.[state_desc]='online'
AND [d].[is_read_only]=0

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @sql;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'USE [' + @sql + ']; '+CHAR(13)+CHAR(10) +
               'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ''' + @loginName + ''') '+CHAR(13)+CHAR(10) +
               'BEGIN '+CHAR(13)+CHAR(10) +
               '    CREATE USER [' + @loginName + '] FOR LOGIN [' + @loginName + ']; '+CHAR(13)+CHAR(10) +
               'END '+CHAR(13)+CHAR(10) +
               'ALTER USER [' + @loginName + '] WITH DEFAULT_SCHEMA = dbo; '+CHAR(13)+CHAR(10) +
               'EXEC sp_addrolemember ''db_ddladmin'', ''' + @loginName + '''; '+CHAR(13)+CHAR(10) +
               'EXEC sp_addrolemember ''db_datareader'', ''' + @loginName + '''; '+CHAR(13)+CHAR(10) +
               'EXEC sp_addrolemember ''db_datawriter'', ''' + @loginName + ''';'+CHAR(13)+CHAR(10)+
               'GRANT EXECUTE TO [' + @loginName + '];'+CHAR(13)+CHAR(10)+
               '------------------------------------'+CHAR(13)+CHAR(10);

    EXEC sp_executesql @sql;
    PRINT @sql;
    FETCH NEXT FROM db_cursor INTO @sql;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;

--ROLLBACK TRANSACTION 
COMMIT TRANSACTION