Tenere sincronizzate le utenze tra server diversi Microsoft SQL server 2008 o 2005. - Lo Scriptomane - livello AVANZATO.

A volte è necessario inventarsi delle soluzioni proprie a problemi noti.
Queste sono proprio le sfide che fanno entrare in gioco gli scriptomani...
Qualcuno costruisce la sua piccola fortuna vendendole a caro prezzo, io le regalo a voi...

Ad esempio, ci sono N situazioni, in cui ci si può mettere, che richiedono di mantenere aggiornate le utenze tra più server SQL (ok, ok, solo se si è sistemisti e si lavora con SQL in aziende ad un certo livello, altrimenti hanno un unico server database e il problema non si pone... Già... e ringraziare quando hanno un server per il database ed uno diverso per il dominio e per la posta... LOL).

Una di queste situazioni è quando si crea un SQL mirroring ed il database è acceduto da tutti gli utenti windows dell'azienda, perchè il gestionale utilizza le credenziali windows per la connessione al database. E' una cosa normalissima e, anzi, molto utile. E' il caso di Microsoft® Navision® e di tanti altri. 
Altre occasioni possono essere un database "mirrorato" molto modificato sulle permissions, oppure Utenti Windows che accedono al DB che devono cambiare nel tempo, per un motivo o per l'altro.



Microsoft offre degli strumenti favolosi per replicare, trasferire, backuppare i dati, ma da SQL server 2005 in avanti c'è un "behaviour by design" (comportamento di default) che mal si sposa con la maggior parte delle situazioni in cui si ha bisogno di amministrare un DB su più servers:
gli utenti sono registrati sia a livello di servers che a livello di database, cosicchè si ha l'antipatica situazione in cui il database potrebbe contenere delle utenze che il server non contiene (si parla di "orphaned users").

Quegli utenti non solo non riusciranno ad accedere, ma saranno completamente fuori sincronia anche nel momento in cui si ricreasse l'utente windows a livello di server. Per riattivarli si dovranno cancellare dal DB e poi ricrearli sia nel server che nel DB (anche con un unica procedura).

Purtroppo la mamma di SQL non ha pensato a questa situazione, offrendo stumenti integrati: ha lasciato a noi "scriptomani" il compito di farlo!

Per fortuna qualcosa c'è: esiste un articolo della KB che mostra uno script per aggiornare le utenze di un server SQL remoto, partendo da uno locale. Purtroppo questo script è a tre fasi e non è automatizzabile (scriptabile) e quindi non ci piace... Quello che voglio fare qui è creare uno script che si possa schedulare su SQL perchè si "arrangi" a tenere sincronizzate le utenze quando meglio ci fa comodo.

Allora ecco qui lo script SQL che ho realizzato partendo da questo articolo della KB, insieme al mio "collega" Cladio C. (tanto dell'adeguamento è opera sua).



USE master
GO
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb nvarchar(max)

DECLARE login_curs CURSOR FOR
      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
      [].msdb.sys.server_principals p LEFT JOIN [].msdb.sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'G', 'U' ) AND p.name <> 'sa' AND p.name <> 'NT AUTHORITY\SERVIZIO DI RETE' AND p.name collate Latin1_General_CI_AS NOT IN (SELECT name from sys.server_principals) AND p.is_disabled = 0
OPEN login_curs

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin

    WHILE (@@fetch_status <> -1)
    BEGIN
          IF (@@fetch_status <> -2)
          BEGIN
           
            SET @tmpstr = '-- Login: ' + @name
           
            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
           
            IF (@denylogin = 1)
            BEGIN -- login is denied access
              SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
            END
            ELSE IF (@hasaccess = 0)
            BEGIN -- login exists but does not have access
              SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
            END
            IF (@is_disabled = 1)
            BEGIN -- login is disabled
              SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
            END
           
            EXECUTE(@tmpstr)
            PRINT 'Utente ' + @name + ' Creato!'
           
          END

            FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
    END
CLOSE login_curs
DEALLOCATE login_curs

Prima di eseguire lo script (che si esegue sul server di DESTINAZIONE), si deve aggiungere il server di origine come server collegato, in modo da permetterci di fare query sul server come se fosse in locale.
Per fare questo, da SQL Server Management Studio:

Collegati all'istanza di DESTINAZIONE
Espandi "Oggetti Server" -> "server collegati"
se il server non è già presente, fai click di destro sul nodo "server collegati" e scegli "nuovo server collegato..." (vedi immagine 1)

immagine 1

Inserisci il nome del server collegato (io metto sempre il FQDN del server) nella scheda generale, seleziona SQL Server  e spostati nella scheda protezione.
Qui seleziona semplicemente la spunta "Verranno effettuate con il contesto di protezione corrente dell'account di accesso" e poi dai OK (immagine 2).

immagine2

A questo punto vedremo il server collegato.


Ora che abbiamo visto i prerequisiti, guardiamo la parte più interessante... lo script!
Commentiamolo insieme:



USE master
GO
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb nvarchar(max)

dichiaro le variabili iniziali

 DECLARE login_curs CURSOR FOR

Start di un cursore in cui verranno messi i risultati che vado a leggere con al query successiva.

SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
      [].msdb.sys.server_principals p LEFT JOIN [].msdb.sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'G', 'U' ) AND p.name <> 'sa' AND p.name <> 'NT AUTHORITY\SERVIZIO DI RETE' AND p.name collate Latin1_General_CI_AS NOT IN (SELECT name from sys.server_principals) AND p.is_disabled = 0

Letteralmente: SELEZIONA i campi che ci interessano DALLA tabella msdb.sys.server_principals del server COLLEGATO (ORIGINE), unita alla tabella syslogins, DOVE il "type" è "G" o "U" (ovvero gli utenti WINDOWS) E il nome è diverso da "sa" E il nome è diverso da 'NT AUTHORITY\SERVIZIO DI RETE' E il nome (in collation del server di ORIGINE **) NON E' già presente nella tabella dei logins locale (quindi nel server di DESTINAZIONE).



OPEN login_curs

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin

    WHILE (@@fetch_status <> -1)



Apri il cursore e cicla queste azioni per ogni record, fino alla fine (quando @@fetc_status sarà = a -1) 


    BEGIN
          IF (@@fetch_status <> -2)
          BEGIN
           
            SET @tmpstr = '-- Login: ' + @name
           
            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
           
            IF (@denylogin = 1)
            BEGIN -- login is denied access
              SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
            END
            ELSE IF (@hasaccess = 0)
            BEGIN -- login exists but does not have access
              SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
            END
            IF (@is_disabled = 1)
            BEGIN -- login is disabled
              SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
            END
           
            EXECUTE(@tmpstr)
            PRINT 'Utente ' + @name + ' Creato!'
 

Costruisco la query che andrà eseguita con i pezzi recuperati, poi la eseguo (EXECUTE) e mando un messaggio in console (PRINT) che riporta il nome utente creato.            
          END  

fine del set di operazioni da ciclare.

            FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
    END
CLOSE login_curs
DEALLOCATE login_curs

leggi il prossimo record e, se presente, ri-cicla, altrimenti finisci (END), poi chiudi il cursore e libera la memoria.

L'ho schedulato e sincronizza tutte le utenze da un mesetto...

FIKO, no?

Ciao a tutti e alla prossima!