none
Ripristino trasforma utente con accesso in utente senza accesso RRS feed

  • Domanda

  • Buongiorno,

    Ho un database realizzato (non da me) su SQLServer 2014 che utilizzo in due copie su due istanze SQLServer separate nello stesso server, una in ambiente di produzione ed una di test, con una serie di utenti di database (8 per l'esattezza), due dei quali (dbo e finance) sono utenti con account di accesso collegati a due account di accesso distinti (sa e finance rispettivamente).
    Ogni volta che devo ripristinare un backup da ambiente di produzione nell'ambiente di test, l'utente dbo, collegato a sa, rimane definito come utente con account di accesso, l'altro, collegato all'account di accesso creato ad oc su un server differente, anche se esiste già, lo scollega sempre e diventa un utente senza account di accesso.

    Ogni ripristino devo andare su SQLManagement Studio, selezionare lo schema di default, collegarlo ad un altro utente, cancellare l'utente, crearlo di nuovo, stavolta collegato, dargli tutti gli user grant di cui necessita (sono diversi, rischio di perdermi) e collegarlo allo schema.

    Ho provato a fare tutte le operazioni salvando uno script, ma se eseguo lo script, l'accesso al DB tramite l'account finance ritorna picche, l'unico sistema è quello di fare tutto interattivamente.

    Qui di seguito lo script che è stato generato:

    ALTER AUTHORIZATION ON SCHEMA::[FINANCE] TO [dbo]
    GO
    DROP USER [FINANCE]
    GO
    CREATE USER [FINANCE] FOR LOGIN [FINANCE] WITH DEFAULT_SCHEMA=[FINANCE]
    GO
    ALTER AUTHORIZATION ON SCHEMA::[FINANCE] TO [FINANCE]
    GO
     ------------------------------------------------
     -- Per tutti gli schemi (il cui nome è @@) --
     ------------------------------------------------
    GRANT UPDATE ON SCHEMA::@@ TO [FINANCE]
    GRANT DELETE ON SCHEMA::@@ TO [FINANCE]
    GRANT INSERT ON SCHEMA::@@ TO [FINANCE]
    GRANT ALTER ON SCHEMA::@@ TO [FINANCE]
    GRANT SELECT ON SCHEMA::@@ TO [FINANCE]
    GO

    Le mie domande:

    1: Perché il ripristino non riesce a collegare l'utente all'account? (mi immagino perché ci sia indicato un SID anziché il nome nel backup, ma c'è un modo di fare il viceversa?)

    2: Perché lo script non funziona? Cosa manca?

    3: Esiste uno script semplificato che trasformi da utente senza account ad utente con account, anziché dover necessariamente indicare manualmente i grant da interfaccia?
    Ho provato a vedere ALTER USER, ma negli help-on-line è specificato

    «The WITH LOGIN clause enables the remapping of a user to a different login. Users without a login, [...] cannot be re-mapped with this clause»
    Ho provato anche a vedere se sp_change_users_login potesse fare al caso mio, ma anche qui
    sp_change_users_login cannot be used with a SQL Server login created from a Windows principal or with a user created by using CREATE USER WITHOUT LOGIN




    lunedì 9 ottobre 2017 10:49

Risposte

  • Ciao Lucio,

    Il problema di fondo è che due login, con stesso login name e stessa password, creati su due istanze diverse hanno SID diverso, quindi lo user mapping del login sul database su ciascuna istanza sarà relativo a due login diversi. Quando fai il restore del db tra un'istanza e l'altra, SQL Server non riesce a ricostruire lo user mapping, portandoti ad avere gli user sul db senza il corrispondente login.

    La soluzione è quella di creare il login "finance" con lo stesso SID su entrambe le istanze, e puoi farlo usando lo script che trovi qui:

    https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

    Eseguendo lo script avrai in output il comando CREATE LOGIN necessario per creare il login con il SID specifico, che, eseguendolo su entrambe le istanze, ti permetterà di avere il login con lo stesso SID e di fare il restore diretto del database tra le due istanze senza perdere lo user mapping.

    HTH


    Alberto Dallagiacoma
    My Italian Blog: http://blogs.ugidotnet.org/alby
    Twitter: http://twitter.com/albertodall
    DotDotNet - User Group .NET Emilia Romagna: http://www.dotdotnet.org


    giovedì 12 ottobre 2017 12:35

Tutte le risposte

  • utilizzando lo script per ricreare il login, quale errore ottieni quando tenti di connetterti con l'account finance ?

    Edoardo Benussi
    Microsoft MVP - Cloud and Datacenter Management
    e[dot]benussi[at]outlook[dot]it

    lunedì 9 ottobre 2017 12:53
    Moderatore
  • Ciao Lucio,

    Il problema di fondo è che due login, con stesso login name e stessa password, creati su due istanze diverse hanno SID diverso, quindi lo user mapping del login sul database su ciascuna istanza sarà relativo a due login diversi. Quando fai il restore del db tra un'istanza e l'altra, SQL Server non riesce a ricostruire lo user mapping, portandoti ad avere gli user sul db senza il corrispondente login.

    La soluzione è quella di creare il login "finance" con lo stesso SID su entrambe le istanze, e puoi farlo usando lo script che trovi qui:

    https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

    Eseguendo lo script avrai in output il comando CREATE LOGIN necessario per creare il login con il SID specifico, che, eseguendolo su entrambe le istanze, ti permetterà di avere il login con lo stesso SID e di fare il restore diretto del database tra le due istanze senza perdere lo user mapping.

    HTH


    Alberto Dallagiacoma
    My Italian Blog: http://blogs.ugidotnet.org/alby
    Twitter: http://twitter.com/albertodall
    DotDotNet - User Group .NET Emilia Romagna: http://www.dotdotnet.org


    giovedì 12 ottobre 2017 12:35
  • Ero sicuro che il problema fosse quello dei SID. Il problema è stato risolto correttamente. Grazie
    lunedì 23 ottobre 2017 09:08
  • Buona sera devi prepararti uno script per ri-allineare i SID che puoi usare ogni qualvolta hai la necessita di migrare i dati tra ambienti diversi tramite procedura di backup.

    Saluti

    Qurino


    QuirinoS

    lunedì 6 novembre 2017 13:43