Trasferire account di accesso e password tra istanze di SQL Server

Questo articolo descrive come trasferire gli account di accesso e le password tra istanze diverse di SQL Server in esecuzione in Windows.

Versione originale del prodotto: SQL Server
Numero originale della Knowledge Base: 918992, 246133

Introduzione

Questo articolo descrive come trasferire gli account di accesso e le password tra istanze diverse di Microsoft SQL Server.

Nota

Le istanze potrebbero trovarsi nello stesso server o in server diversi e le relative versioni potrebbero essere diverse.

Altre informazioni

In questo articolo il server A e il server B sono server diversi.

Dopo aver spostato un database dall'istanza di SQL Server nel server A all'istanza di SQL Server nel server B, gli utenti potrebbero non essere in grado di accedere al database nel server B. Inoltre, gli utenti potrebbero ricevere il messaggio di errore seguente:

Accesso non riuscito per l'utente "Utente". (Microsoft SQL Server, Errore: 18456)

Questo problema si verifica perché gli account di accesso e le password non sono stati trasferiti dall'istanza di SQL Server nel server A all'istanza di SQL Server nel server B.

Nota

Il messaggio di errore 18456 si verifica anche per altri motivi. Per altre informazioni su queste cause e sulle possibili risoluzioni, vedere MSSQLSERVER_18456.

Per trasferire gli accessi, utilizzare uno dei metodi seguenti, in base alla situazione.

  • Metodo 1: Reimpostare la password nel computer SQL Server di destinazione (Server B).

    Per risolvere questo problema, reimpostare la password nel computer SQL Server e quindi inserire nello script l'account di accesso.

    Nota

    Quando si reimposta la password, viene utilizzato l'algoritmo hash delle password.

  • Metodo 2: trasferire account di accesso e password al server di destinazione (Server B) usando script generati nel server di origine (Server A).

    1. Creare stored procedure che consentono di generare gli script necessari per trasferire gli account di accesso e le relative password. A tale scopo, connettersi al server A utilizzando SQL Server Management Studio (SSMS) oppure qualsiasi altro strumento client ed eseguire lo script seguente:

        USE [master]
        GO
        IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
        DROP PROCEDURE sp_hexadecimal
        GO
        CREATE PROCEDURE [dbo].[sp_hexadecimal]
        (
            @binvalue varbinary(256),
            @hexvalue varchar (514) OUTPUT
        )
        AS
        BEGIN
            DECLARE @charvalue varchar (514)
            DECLARE @i int
            DECLARE @length int
            DECLARE @hexstring char(16)
            SELECT @charvalue = '0x'
            SELECT @i = 1
            SELECT @length = DATALENGTH (@binvalue)
            SELECT @hexstring = '0123456789ABCDEF'
      
            WHILE (@i <= @length)
            BEGIN
                  DECLARE @tempint int
                  DECLARE @firstint int
                  DECLARE @secondint int
      
                  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
                  SELECT @firstint = FLOOR(@tempint/16)
                  SELECT @secondint = @tempint - (@firstint*16)
                  SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1)
      
                  SELECT @i = @i + 1
            END 
            SELECT @hexvalue = @charvalue
        END
        go
        IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
        DROP PROCEDURE sp_help_revlogin
        GO
        CREATE PROCEDURE [dbo].[sp_help_revlogin]   
        (
            @login_name sysname = NULL 
        )
        AS
        BEGIN
            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 @Prefix                   VARCHAR(255)
            DECLARE @defaultdb                SYSNAME
            DECLARE @defaultlanguage          SYSNAME     
            DECLARE @tmpstrRole               VARCHAR (1024)
      
        IF (@login_name IS NULL)
        BEGIN
            DECLARE login_curs CURSOR 
            FOR 
                SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name  
                FROM  sys.server_principals p 
                LEFT JOIN sys.syslogins     l ON ( l.name = p.name ) 
                WHERE p.type IN ( 'S', 'G', 'U' ) 
                AND p.name <> 'sa'
                AND p.name not like '##%'
                ORDER BY p.name
        END
        ELSE
                DECLARE login_curs CURSOR 
                FOR 
                    SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name  
                    FROM  sys.server_principals p 
                    LEFT JOIN sys.syslogins        l ON ( l.name = p.name ) 
                    WHERE p.type IN ( 'S', 'G', 'U' ) 
                      AND p.name = @login_name
                    ORDER BY p.name
      
                OPEN login_curs 
                FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage 
                IF (@@fetch_status = -1)
                BEGIN
                      PRINT 'No login(s) found.'
                      CLOSE login_curs
                      DEALLOCATE login_curs
                      RETURN -1
                END
      
                SET @tmpstr = '/* sp_help_revlogin script '
                PRINT @tmpstr
      
                SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
      
                PRINT @tmpstr
                PRINT ''
      
                WHILE (@@fetch_status <> -1)
                BEGIN
                  IF (@@fetch_status <> -2)
                  BEGIN
                        PRINT ''
      
                        SET @tmpstr = '-- Login: ' + @name
      
                        PRINT @tmpstr
      
                        SET @tmpstr='IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''+@name+''')
                        BEGIN'
                        Print @tmpstr 
      
                        IF (@type IN ( 'G', 'U'))
                        BEGIN -- NT authenticated account/group 
                          SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']' + ', DEFAULT_LANGUAGE = [' + @defaultlanguage + ']'
                        END
                        ELSE 
                        BEGIN -- SQL Server authentication
                                -- obtain password and sid
                                SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
      
                                EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
                                EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
      
                                -- obtain password policy state
                                SELECT @is_policy_checked     = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END 
                                FROM sys.sql_logins 
                                WHERE name = @name
      
                                SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END 
                                FROM sys.sql_logins 
                                WHERE name = @name
      
                                SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' 
                                                + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']' + ', DEFAULT_LANGUAGE = [' + @defaultlanguage + ']'
      
                                IF ( @is_policy_checked IS NOT NULL )
                                BEGIN
                                  SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
                                END
      
                                IF ( @is_expiration_checked IS NOT NULL )
                                BEGIN
                                  SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
                                END
                END
      
                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 
      
                SET @Prefix = '
                EXEC master.dbo.sp_addsrvrolemember @loginame='''
      
                SET @tmpstrRole=''
      
                SELECT @tmpstrRole = @tmpstrRole
                    + CASE WHEN sysadmin        = 1 THEN @Prefix + [LoginName] + ''', @rolename=''sysadmin'''        ELSE '' END
                    + CASE WHEN securityadmin   = 1 THEN @Prefix + [LoginName] + ''', @rolename=''securityadmin'''   ELSE '' END
                    + CASE WHEN serveradmin     = 1 THEN @Prefix + [LoginName] + ''', @rolename=''serveradmin'''     ELSE '' END
                    + CASE WHEN setupadmin      = 1 THEN @Prefix + [LoginName] + ''', @rolename=''setupadmin'''      ELSE '' END
                    + CASE WHEN processadmin    = 1 THEN @Prefix + [LoginName] + ''', @rolename=''processadmin'''    ELSE '' END
                    + CASE WHEN diskadmin       = 1 THEN @Prefix + [LoginName] + ''', @rolename=''diskadmin'''       ELSE '' END
                    + CASE WHEN dbcreator       = 1 THEN @Prefix + [LoginName] + ''', @rolename=''dbcreator'''       ELSE '' END
                    + CASE WHEN bulkadmin       = 1 THEN @Prefix + [LoginName] + ''', @rolename=''bulkadmin'''       ELSE '' END
                  FROM (
                            SELECT CONVERT(VARCHAR(100),SUSER_SNAME(sid)) AS [LoginName],
                                    sysadmin,
                                    securityadmin,
                                    serveradmin,
                                    setupadmin,
                                    processadmin,
                                    diskadmin,
                                    dbcreator,
                                    bulkadmin
                            FROM sys.syslogins
                            WHERE (       sysadmin<>0
                                    OR    securityadmin<>0
                                    OR    serveradmin<>0
                                    OR    setupadmin <>0
                                    OR    processadmin <>0
                                    OR    diskadmin<>0
                                    OR    dbcreator<>0
                                    OR    bulkadmin<>0
                                ) 
                                AND name=@name 
                      ) L 
      
                    PRINT @tmpstr
                    PRINT @tmpstrRole
                    PRINT 'END'
                END 
                FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage 
            END
            CLOSE login_curs
            DEALLOCATE login_curs
            RETURN 0
        END
      

      Nota

      Questo script crea due stored procedure nel database master. Le procedure sono denominate sp_hexadecimal e sp_help_revlogin.

    2. Nell'editor di query SSMS, selezionare l'opzione Risultati in formato testo.

    3. Eseguire l'istruzione seguente nella stessa finestra di query oppure in una nuova:

      EXEC sp_help_revlogin
      
    4. Lo script di output generato dalla store procedure sp_help_revlogin è lo script di accesso. Questo script di accesso crea gli account di accesso con l'identificatore di sicurezza (SID) originale e la password originale.

Importante

Esaminare le informazioni nella sezione Osservazioni seguente prima di procedere con la procedura di implementazione nel server di destinazione.

Procedure nel server di destinazione (Server B)

Connettersi al server B usando qualsiasi strumento client (ad esempio SSMS) e quindi eseguire lo script generato nel passaggio 4 (output di sp_helprevlogin) dal server A.

Osservazioni

Prima di eseguire lo script di output nell'istanza del server B, esaminare le informazioni seguenti:

  • È possibile eseguire l'hash di una password nei modi seguenti:

    • VERSION_SHA1: questo hash viene generato utilizzando l'algoritmo SHA1 e viene utilizzato in SQL Server 2000 fino a SQL Server 2008 R2.
    • VERSION_SHA2: questo hash viene generato utilizzando l'algoritmo SHA2 512 e viene utilizzato in SQL Server 2012 e versioni successive.
  • Controllare attentamente lo script di output. Se il server A e il server B si trovano in domini diversi, è necessario modificare lo script di output. È quindi necessario sostituire il nome di dominio originale usando il nuovo nome di dominio nelle CREATE LOGIN istruzioni . Gli account di accesso integrati a cui viene concesso l'accesso nel nuovo dominio non hanno lo stesso SID degli account di accesso nel dominio originale. Di conseguenza, gli utenti sono isolati da questi account di accesso. Per altre informazioni su come risolvere questi utenti orfani, vedere Risolvere i problemi degli utenti orfani (SQL Server) e ALTER USER.
    Se il server A e il server B si trovano nello stesso dominio, viene utilizzato lo stesso SID. Pertanto, è improbabile che gli utenti siano isolati.

  • Nello script di output gli account di accesso vengono creati utilizzando la password crittografata. Ciò è dovuto all'argomento su cui è stato eseguito l'hashing nell'istruzione CREATE LOGIN. Questo argomento specifica che l'hashing della password immessa dopo l'argomento PASSWORD è già stata eseguita.

  • Per impostazione predefinita, solo un membro del ruolo predefinito del server sysadmin può eseguire un'istruzione SELECT dalla visualizzazione sys.server_principals. A meno che un membro del ruolo predefinito del server sysadmin non conceda le autorizzazioni necessarie agli utenti, gli utenti non possono creare o eseguire lo script di output.

  • I passaggi descritti in questo articolo non trasferiscono le informazioni di database predefinite per un account di accesso specifico. Questo perché il database predefinito potrebbe non esistere sempre nel server B. Per definire il database predefinito per un account di accesso, usare l'istruzione ALTER LOGIN passando il nome dell'account di accesso e il database predefinito come argomenti.

  • Ordinare i server di origine e di destinazione:

    • Server senza distinzione tra maiuscole e minuscole A e server B con distinzione tra maiuscole e minuscole: l'ordinamento del server A potrebbe non fare distinzione tra maiuscole e minuscole e l'ordinamento del server B potrebbe fare distinzione tra maiuscole e minuscole. In questo caso, gli utenti devono digitare le password interamente in lettere maiuscole dopo aver trasferito i login e le password all'istanza sul server B.

    • Server con distinzione tra maiuscole e minuscole A e server B senza distinzione tra maiuscole e minuscole: L'ordinamento del server A potrebbe fare distinzione tra maiuscole e minuscole e l'ordinamento del server B potrebbe non fare distinzione tra maiuscole e minuscole. In questo caso, gli utenti non possono accedere usando gli account di accesso e le password trasferiti all'istanza nel server B a meno che non sia vera una delle condizioni seguenti:

      • Le password originali non contengono lettere.
      • Tutte le lettere nelle password originali sono lettere maiuscole.
    • Distinzione tra maiuscole e minuscole o senza distinzione tra maiuscole e minuscole in entrambi i server: l'ordinamento del server A e del server B potrebbe fare distinzione tra maiuscole e minuscole oppure l'ordinamento del server A e del server B potrebbe non fare distinzione tra maiuscole e minuscole. In questi casi, gli utenti non riscontrano un problema.

  • Un account di accesso già presente nell'istanza del server B potrebbe avere un nome uguale a un nome nello script di output. In questo caso, viene visualizzato il seguente messaggio di errore quando si esegue lo script di output sull'istanza del server B:

    Msg 15025, livello 16, stato 1, riga 1
    L'entità del server "MyLogin" esiste già.

    Analogamente, un account di accesso già presente nell'istanza del server B potrebbe avere un SID uguale a un SID nello script di output. In questo caso, viene visualizzato il seguente messaggio di errore quando si esegue lo script di output sull'istanza del server B:

    Msg 15433, livello 16, stato 1, linea 1 SID del parametro fornito è in uso.

    Pertanto, è necessario procedere come segue:

    1. Controllare attentamente lo script di output.

    2. Esaminare il contenuto della sys.server_principals visualizzazione nell'istanza del server B.

    3. Risolvere questi messaggi di errore come appropriato.

      In SQL Server 2005, il SID di un login viene utilizzato per implementare l'accesso a livello di database. Un account di accesso potrebbe avere SID diversi in database diversi in un server. In questo caso, l'account di accesso può accedere solo al database con SID corrispondente al SID nella vista sys.server_principals. Questo problema può verificarsi se i due database vengono combinati da server diversi. Per risolvere questo problema, rimuovere manualmente l'account di accesso dal database che presenta un SID non corrispondente, utilizzando l'istruzione DROP USER. Aggiungere quindi di nuovo l'account di accesso usando l'istruzione CREATE USER.

Riferimenti