/*
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
    DROP LOGIN [@login@];
    PRINT ''Dropped login [@login@]'';
END
';
DECLARE @tplRight NVARCHAR(MAX) = N'
USE @dbName@
IF 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
    ALTER ROLE [db_owner] DROP MEMBER [@login@]
    DROP USER [@login@]
    PRINT ''Dropped user [@login@] in @dbName@'';
END 

';

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 remove 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 remove rights to db's

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

--PRINT @q;

begin tran;
EXEC (@q);
rollback tran;