/*
Server:		samnb705db01.amavita.ch
Format:		GCM 
Business:	TPPHAR
type:		VALI
Version:	23.1.21006.00066

28.02.2024, TSC
*/
USE master;

DECLARE @q NVARCHAR(MAX) = N'';
DECLARE @tplLogin NVARCHAR(MAX)
  = N'
IF EXISTS (SELECT 1 FROM master.dbo.syslogins WHERE name = ''@login@'')
BEGIN
    ALTER LOGIN [@login@]
    WITH DEFAULT_DATABASE = [master],
         DEFAULT_LANGUAGE = [us_english]
         --CHECK_EXPIRATION = OFF,
         --CHECK_POLICY = ON;
    ALTER LOGIN [@login@] ENABLE;
END;
ELSE
BEGIN
    CREATE LOGIN [@login@] FROM WINDOWS 
    WITH DEFAULT_DATABASE = [master],
         DEFAULT_LANGUAGE = [us_english]
         --CHECK_EXPIRATION = OFF,
         --CHECK_POLICY = ON;
END;
';
DECLARE @tplRight NVARCHAR(MAX) = N'
USE @dbName@
IF NOT EXISTS(
   SELECT *
    FROM sys.database_principals AS dp
    INNER JOIN sys.server_principals AS sp ON dp.sid = sp.sid
    WHERE sp.type_desc IN (''WINDOWS_LOGIN'', ''SQL_LOGIN'')
    AND sp.name = ''@login@''  
)
BEGIN
    CREATE USER [@login@] FOR LOGIN [@login@]
END 

ALTER ROLE [db_owner] ADD MEMBER [@login@]
';

DECLARE @users TABLE (login VARCHAR(255) NOT NULL,
                      name VARCHAR(255) NOT NULL);
INSERT INTO @users ([login],
                    [name])
VALUES ('CENTRALINFRA\ua253440', 'Oliver'),
       ('CENTRALINFRA\ua253450', 'Franck'),
       ('CENTRALINFRA\ua193890','Muhamed'),
       ('CENTRALINFRA\ua210510', 'Wyn');

--#region create logins
SELECT @q = @q + REPLACE(@tplLogin, '@login@', [u].[login])
  FROM @users u;
--#endregion create logins

--#region give rights to db's
/* declare variables */
DECLARE @dbName NVARCHAR(255),
        @login  VARCHAR(255);

DECLARE csrDb CURSOR FAST_FORWARD READ_ONLY FOR
SELECT      [d].[name],
            [u].[login]
  FROM      sys.databases d
 CROSS JOIN @users u
 WHERE      LOWER([d].[name]) IN ( 'arizona', 'arizonarep', 'hcitools', 'arizonacust', 'activepos_read', 'activepos_write' );

OPEN csrDb;

FETCH NEXT FROM csrDb
 INTO @dbName,
      @login;

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @q = @q + REPLACE(REPLACE(@tplRight, '@dbName@', @dbName), '@login@', @login);

  FETCH NEXT FROM csrDb
   INTO @dbName,
        @login;
END;

CLOSE csrDb;
DEALLOCATE csrDb;
--#endregion give rights to db's

--PRINT @q;

EXEC (@q);