sql-scripts/HCI - setup new partner_superset.sql

/*
partner_centralized_superset

Wire the database Users to these new AD groups

    DEV L-CI-AP-SQL-D-HCI-Partner_Centralized_Superset_R and L-CI-AP-SQL-D-HCI-Partner_Centralized_Superset_RW

    INT L-CI-AP-SQL-I-HCI-Partner_Centralized_Superset_R and L-CI-AP-SQL-I-HCI-Partner_Centralized_Superset_RW

Copy some other legacy groups/users/sql users from Partner_Superset on the new database partner_centralized_superset

DEV only yellow marked groups from Partner_Superset

25.09.2025, TSC
*/

BEGIN TRANSACTION 

SET XACT_ABORT ON;
SET NOCOUNT ON;

DECLARE @q NVARCHAR(MAX)='';
DECLARE @db sysname='partner_centralized_superset';
DECLARE @env VARCHAR(111);
DECLARE @groups TABLE(env VARCHAR(111) NOT NULL, grp_name VARCHAR(111) NOT NULL);
SELECT @env = CASE 
    WHEN @@SERVERNAME='SWMDATASQLDEV01' THEN 'dev'
    WHEN @@SERVERNAME='SWMDATASQLINT01' THEN 'int'
    WHEN @@SERVERNAME='SWMDATASQLPRD01' THEN 'prod'
    WHEN @@SERVERNAME='SWSQLMDQAS05'    THEN 'log'
    ELSE 'unknown'
END;
DECLARE @tplLogins NVARCHAR(MAX)='
IF NOT EXISTS (
    SELECT 1
    FROM sys.server_principals
    WHERE name = ''@grp@''
)
BEGIN
    SET @sql = 
        N''CREATE LOGIN [@grp@] '' +
        N''FROM WINDOWS '' +
        N''WITH DEFAULT_DATABASE=[master];'';
    EXEC (@sql);
    
    PRINT ''created login [@grp@]'';
END

';
DECLARE @tplUser NVARCHAR(MAX)='
IF NOT EXISTS (
    SELECT 1
    FROM sys.database_principals
    WHERE name = N''@grp@''
)
BEGIN
    CREATE USER [@grp@] 
    FOR LOGIN [@grp@];
    PRINT ''Added user [@grp@]'';
END 

IF EXISTS (
    SELECT 1
    FROM sys.database_principals
    WHERE name = N''@grp@''
)
BEGIN
    GRANT EXECUTE TO [@grp@];
    --PRINT ''Granted EXECUTE to [@grp@]'';
    
    ALTER ROLE [db_datareader] ADD MEMBER [@grp@]
    --PRINT ''Granted db_datareader to [@grp@]'';

    PRINT ''Granted read and execute permissions to [@grp@]'';
    IF ''@grp@'' like ''%[_]RW''
    BEGIN
        ALTER ROLE [db_datawriter] ADD MEMBER [@grp@];
        PRINT ''Granted db_datawriter to [@grp@]'';
    END 

    
END
 
'

INSERT INTO @groups ([env],[grp_name])
VALUES('dev',  'CENTRALINFRA\L-CI-AP-SQL-D-HCI-Partner_Centralized_Superset_R')
     ,('dev',  'CENTRALINFRA\L-CI-AP-SQL-D-HCI-Partner_Centralized_Superset_RW')
     ,('int',  'CENTRALINFRA\L-CI-AP-SQL-I-HCI-Partner_Centralized_Superset_R')
     ,('int',  'CENTRALINFRA\L-CI-AP-SQL-I-HCI-Partner_Centralized_Superset_RW')
     ,('prod', 'CENTRALINFRA\L-CI-AP-SQL-P-HCI-Partner_Centralized_Superset_R')
     ,('prod', 'CENTRALINFRA\L-CI-AP-SQL-P-HCI-Partner_Centralized_Superset_RW')

     --specific groups to add on dev
     ,('dev','CENTRALINFRA\L-CI-AP-SQL-DEV_MED_Team')
     ,('dev','CENTRALINFRA\msa-IIS-EMedipI$')
     ,('dev','CENTRALINFRA\svc-DoMe-Dev')
     ,('dev','E-MEDIAT\au_cp2_int')
     ,('dev','E-MEDIAT\au_documedis')
     ,('dev','E-MEDIAT\au_MyProduct_int')
     ,('dev','E-MEDIAT\au_ws_getpartner')
     ,('dev','medicalData-importer-dev')
     ,('dev','newrelic')

     --specific groups to add on int
     ,('int','centralinfra\L-CI-AP-SQL-INT_MED_Team')
     ,('int','CENTRALINFRA\msa-IIS-EMedipI$')
     ,('int','CENTRALINFRA\svc-DoMe-Int')
     ,('int','E-MEDIAT\au_cp2_int')
     ,('int','E-MEDIAT\au_documedis')
     ,('int','E-MEDIAT\au_MyProduct_int')
     ,('int','E-MEDIAT\dbgrp_EMEVDB22_PARTNER_SUPERSET_R')
     ,('int','medicalData-importer-int')
     ,('int','newrelic')

     --specific groups to add on prod
     ,('prod','CENTRALINFRA\L-CI-AP-SQL-PROD_MED_Team')
     ,('prod','CENTRALINFRA\msa-IIS-EMedipP$')
     ,('prod','CENTRALINFRA\svc-DoMe-Int')
     ,('prod','E-MEDIAT\dbgrp_EMEVDB32_ ODB_Superset_R')
     ,('prod','E-MEDIAT\au_documedis')
     ,('prod','E-MEDIAT\au_MyProduct_Prod')
     ,('prod','E-MEDIAT\au_ws_getpartner')
     ,('prod','E-MEDIAT\dbgrp_EMEVDB32_MyProducts_RW')
     ,('prod','E-MEDIAT\dbgrp_EMEVDB32_PARTNER_SUPERSET_R')
     ,('prod','E-MEDIAT\L-EM-AP-SQL-EMEVDB32_Partner_Superset_R')
     ,('prod','medicalData-importer-prod')
     ,('prod','newrelic')

;
SELECT @env
IF @env IN ('dev','int','prod')
BEGIN
    SET @q='use master
go
BEGIN TRANSACTION 
SET XACT_ABORT ON;
SET NOCOUNT ON;

DECLARE @sql nvarchar(MAX)='''';
';

    --#region login
    SELECT @q = @q + REPLACE(@tplLogins, '@grp@',g.[grp_name])
    FROM @groups g
    WHERE [g].[env] = @env
    AND g.[grp_name] LIKE '%\%';
    --#endregion login

    --#region users
    SET @q = @q +'
use '+@db+'
GO
    ';

    SELECT @q = @q + REPLACE(@tplUser,'@grp@', g.[grp_name])
    FROM @groups g
    WHERE g.[env] = @env;
    --#endregion users

    SET @q = @q +'
ROLLBACK TRANSACTION '
    PRINT ''
    
    --#region print whole dyn sql
    DECLARE @String NVARCHAR(MAX) = @q 
    DECLARE @CurrentEnd BIGINT; /* track the length of the next substring */
    DECLARE @offset tinyint; /*tracks the amount of offset needed */
    set @string = replace(  replace(@string, char(13) + char(10), char(10))   , char(13), char(10))

    WHILE LEN(@String) > 1
    BEGIN
        IF CHARINDEX(CHAR(10), @String) between 1 AND 4000
        BEGIN
               SET @CurrentEnd =  CHARINDEX(char(10), @String) -1
               set @offset = 2
        END
        ELSE
        BEGIN
               SET @CurrentEnd = 4000
                set @offset = 1
        END   
        PRINT SUBSTRING(@String, 1, @CurrentEnd) 
        set @string = SUBSTRING(@String, @CurrentEnd+@offset, LEN(@String))   
    END /*End While loop*/
    --#endregion print whole dyn sql
END 

ROLLBACK TRANSACTION