Condividi tramite

Trasferire account di accesso e password tra istanze di SQL Server

  • Articolo

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 KB originale: 918992, 246133

Introduzione

Questo articolo descrive come trasferire gli account di accesso e le password tra istanze diverse di Microsoft SQL Server. Le istanze potrebbero trovarsi nello stesso server o in server diversi e le relative versioni potrebbero differire.

Perché trasferire gli account di accesso tra istanze di SQL Server?

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

Dopo aver spostato un database da un'istanza di SQL Server nel server A a un'istanza di SQL Server nel server B, gli utenti potrebbero non essere in grado di accedere al server di 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 dall'istanza di SQL Server nel server A non esistono nell'istanza di SQL Server nel server B.

Tenere presente che l'errore 18456 si verifica per molti altri motivi. Per altre informazioni su queste cause e sulle relative risoluzioni, vedere MSSQLSERVER_18456.

Passaggi per trasferire gli account di accesso

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

Metodo 1: Generare script tramite SSMS nel server di origine e reimpostare manualmente le password per gli account di accesso di SQL Server nel server di destinazione

È possibile generare script di accesso in SQL Server Management Studio (SSMS) usando l'opzione Genera script per un database.

Per generare script tramite SSMS nel server di origine e reimpostare manualmente le password per gli account di accesso di SQL Server nel server di destinazione, seguire questa procedura:

  1. Connettersi al server A che ospita l'istanza di SQL Server di origine.

  2. Espandere il nodo di Database.

  3. Fare clic con il pulsante destro del mouse su qualsiasi database utente e selezionare Attività>Genera script.

  4. Si apre la pagina Introduzione. Selezionare Avanti per aprire la pagina Scegli oggetti . Selezionare Genera script per l'intero database e tutti gli oggetti di database.

  5. Selezionare Avanti per aprire la pagina Imposta opzioni di generazione script,

  6. Selezionare il pulsante Avanzate per Opzioni di accesso script.

  7. Nell'elenco Avanzate trovare Account di accesso script, impostare l'opzione su True e selezionare OK.

  8. Tornare a Imposta opzioni di scripting in Selezionare la modalità di salvataggio degli script e selezionare Apri nella nuova finestra di query.

  9. Selezionare Avanti due volte e quindi fine.

  10. Trovare la sezione nello script che contiene gli account di accesso. In genere, lo script generato contiene testo con il commento seguente all'inizio di questa sezione:

    /* For security reasons the login is created disabled and with a random password. */

    Note

    Ciò indica che gli account di accesso di autenticazione di SQL Server vengono generati con una password casuale e sono disabilitati per impostazione predefinita. È necessario reimpostare la password e riabilitare questi account di accesso nel server di destinazione.

  11. Applicare lo script di accesso dallo script generato più grande a SQL Server di destinazione.

  12. Per tutti gli account di accesso di autenticazione di SQL Server, reimpostare la password nell'istanza di SQL Server di destinazione e riabilitare tali account di accesso.

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('dbo.sp_hexadecimal') IS NOT NULL
    DROP PROCEDURE dbo.sp_hexadecimal
GO
CREATE PROCEDURE dbo.sp_hexadecimal
    @binvalue [varbinary](256)
    ,@hexvalue [nvarchar] (514) OUTPUT
AS
BEGIN
    DECLARE @i [smallint]
    DECLARE @length [smallint]
    DECLARE @hexstring [nchar](16)
    SELECT @hexvalue = N'0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH(@binvalue)
    SELECT @hexstring = N'0123456789ABCDEF'
    WHILE (@i < =  @length)
    BEGIN
        DECLARE @tempint   [smallint]
        DECLARE @firstint  [smallint]
        DECLARE @secondint [smallint]
        SELECT @tempint = CONVERT([smallint], SUBSTRING(@binvalue, @i, 1))
        SELECT @firstint = FLOOR(@tempint / 16)
        SELECT @secondint = @tempint - (@firstint * 16)
        SELECT @hexvalue = @hexvalue
            + SUBSTRING(@hexstring, @firstint  + 1, 1)
            + SUBSTRING(@hexstring, @secondint + 1, 1)
        SELECT @i = @i + 1
    END
END
GO
IF OBJECT_ID('dbo.sp_help_revlogin') IS NOT NULL
    DROP PROCEDURE dbo.sp_help_revlogin
GO
CREATE PROCEDURE dbo.sp_help_revlogin
    @login_name [sysname] = NULL
AS
BEGIN
    DECLARE @name                  [sysname]
    DECLARE @type                  [nvarchar](1)
    DECLARE @hasaccess             [int]
    DECLARE @denylogin             [int]
    DECLARE @is_disabled           [int]
    DECLARE @PWD_varbinary         [varbinary](256)
    DECLARE @PWD_string            [nvarchar](514)
    DECLARE @SID_varbinary         [varbinary](85)
    DECLARE @SID_string            [nvarchar](514)
    DECLARE @tmpstr                [nvarchar](4000)
    DECLARE @is_policy_checked     [nvarchar](3)
    DECLARE @is_expiration_checked [nvarchar](3)
    DECLARE @Prefix                [nvarchar](4000)
    DECLARE @defaultdb             [sysname]
    DECLARE @defaultlanguage       [sysname]
    DECLARE @tmpstrRole            [nvarchar](4000)
    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,default_language_name = ISNULL(p.default_language_name,@@LANGUAGE)
        FROM sys.server_principals p
        LEFT JOIN sys.syslogins l ON l.[name] = p.[name]
        WHERE p.[type] IN ('S' /* SQL_LOGIN */,'G' /* WINDOWS_GROUP */,'U' /* WINDOWS_LOGIN */)
            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,default_language_name = ISNULL(p.default_language_name,@@LANGUAGE)
        FROM sys.server_principals p
        LEFT JOIN sys.syslogins l ON l.[name] = p.[name]
        WHERE p.[type] IN ('S' /* SQL_LOGIN */,'G' /* WINDOWS_GROUP */,'U' /* WINDOWS_LOGIN */)
            AND p.[name] <> 'sa'
            AND p.[name] NOT LIKE '##%'
            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 for ' + QUOTENAME(@login_name) + N'. */'
        CLOSE login_curs
        DEALLOCATE login_curs
        RETURN - 1
    END
    SET @tmpstr = N'/* sp_help_revlogin script
** Generated ' + CONVERT([nvarchar], GETDATE()) + N' on ' + @@SERVERNAME + N'
*/'
    PRINT @tmpstr
    WHILE (@@fetch_status <> - 1)
    BEGIN
        IF (@@fetch_status <> - 2)
        BEGIN
            PRINT ''
            SET @tmpstr = N'/* Login ' + QUOTENAME(@name) + N' */'
            PRINT @tmpstr
            SET @tmpstr = N'IF NOT EXISTS (
    SELECT 1
    FROM sys.server_principals
    WHERE [name] = N''' + @name + N'''
    )
BEGIN'
            PRINT @tmpstr
            IF @type IN ('G','U') -- NT-authenticated Group/User
            BEGIN -- NT authenticated account/group 
                SET @tmpstr = N'    CREATE LOGIN ' + QUOTENAME(@name) + N'
    FROM WINDOWS
    WITH DEFAULT_DATABASE = ' + QUOTENAME(@defaultdb) + N'
        ,DEFAULT_LANGUAGE = ' + QUOTENAME(@defaultlanguage)
            END
            ELSE
            BEGIN -- SQL Server authentication
                -- obtain password and sid
                SET @PWD_varbinary = CAST(LOGINPROPERTY(@name, 'PasswordHash') AS [varbinary](256))
                EXEC dbo.sp_hexadecimal @PWD_varbinary, @PWD_string OUT
                EXEC dbo.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 = NCHAR(9) + N'CREATE LOGIN ' + QUOTENAME(@name) + N'
    WITH PASSWORD = ' + @PWD_string + N' HASHED
        ,SID = ' + @SID_string + N'
        ,DEFAULT_DATABASE = ' + QUOTENAME(@defaultdb) + N'
        ,DEFAULT_LANGUAGE = ' + QUOTENAME(@defaultlanguage)

                IF @is_policy_checked IS NOT NULL
                BEGIN
                    SET @tmpstr = @tmpstr + N'
        ,CHECK_POLICY = ' + @is_policy_checked
                END

                IF @is_expiration_checked IS NOT NULL
                BEGIN
                    SET @tmpstr = @tmpstr + N'
        ,CHECK_EXPIRATION = ' + @is_expiration_checked
                END
            END
            IF (@denylogin = 1)
            BEGIN -- login is denied access
                SET @tmpstr = @tmpstr
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'DENY CONNECT SQL TO ' + QUOTENAME(@name)
            END
            ELSE IF (@hasaccess = 0)
            BEGIN -- login exists but does not have access
                SET @tmpstr = @tmpstr
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'REVOKE CONNECT SQL TO ' + QUOTENAME(@name)
            END
            IF (@is_disabled = 1)
            BEGIN -- login is disabled
                SET @tmpstr = @tmpstr
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'ALTER LOGIN ' + QUOTENAME(@name) + N' DISABLE'
            END
            SET @Prefix =
                NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'EXEC [master].dbo.sp_addsrvrolemember @loginame = N'''
            SET @tmpstrRole = N''
            SELECT @tmpstrRole = @tmpstrRole
                + CASE WHEN sysadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''sysadmin''' ELSE '' END
                + CASE WHEN securityadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''securityadmin''' ELSE '' END
                + CASE WHEN serveradmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''serveradmin''' ELSE '' END
                + CASE WHEN setupadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''setupadmin''' ELSE '' END
                + CASE WHEN processadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''processadmin''' ELSE '' END
                + CASE WHEN diskadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''diskadmin''' ELSE '' END
                + CASE WHEN dbcreator = 1 THEN @Prefix + LoginName + N''', @rolename = N''dbcreator''' ELSE '' END
                + CASE WHEN bulkadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''bulkadmin''' ELSE '' END
            FROM (
                SELECT
                    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
            IF @tmpstr <> '' PRINT @tmpstr
            IF @tmpstrRole <> '' 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

    Note

    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.

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

  6. Dopo aver implementato tutti i passaggi applicabili dalla sezione Osservazioni, connettersi al server di destinazione B usando qualsiasi strumento client( ad esempio SSMS).

  7. Eseguire lo script generato come output di sp_helprevlogin dal server A.

Osservazioni:

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

Informazioni sull'hash delle password

  • È 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.

  • Nello script di output gli account di accesso vengono creati utilizzando la password crittografata. Ciò è dovuto all'argomento nell'istruzione HASHEDCREATE LOGIN . Questo argomento specifica che la password immessa dopo l'hash dell'argomento PASSWORD è già stata eseguita.

Come gestire la modifica dei domini

I server di origine e di destinazione sono in domini diversi? 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 relativi agli 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.

Autorizzazioni per visualizzare e selezionare tutti gli account di accesso

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 conceda le autorizzazioni necessarie agli utenti, gli utenti non possono creare o eseguire lo script di output.

L'impostazione predefinita del database non viene inserita in script e trasferita

I passaggi descritti in questo articolo non trasferiscono le informazioni predefinite sul database per un account di accesso specifico. Ciò è dovuto al fatto che il database predefinito potrebbe non esistere sempre nel server B. Per definire il database predefinito per un account di accesso, usare l'istruzione passando il nome dell'account ALTER LOGIN di accesso e il database predefinito come argomenti.

Come gestire diversi ordini di ordinamento tra i server di origine e di destinazione

Potrebbero esserci differenze nell'ordinamento tra i server di origine e di destinazione oppure potrebbero essere uguali. Ecco come risolvere ogni scenario:

  • Server A senza distinzione tra maiuscole e minuscole e server B senza distinzione tra maiuscole e minuscole: l'ordinamento del server A potrebbe non essere 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 essere senza distinzione tra maiuscole e minuscole e l'ordinamento del server B. 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 soddisfatta 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 essere distinzione tra maiuscole e minuscole oppure l'ordinamento del server A e del server B potrebbe non essere distinzione tra maiuscole e minuscole. In questi casi, gli utenti non riscontrano un problema.

Come gestire gli account di accesso già esistenti nel server di destinazione

Lo script è progettato per verificare se l'account di accesso esiste nel server di destinazione e creare un account di accesso solo se non lo è. Tuttavia, se viene visualizzato il messaggio di errore seguente quando si esegue lo script di output nell'istanza nel server B, è necessario risolverlo manualmente seguendo la procedura descritta in questa sezione.

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

Analogamente, un account di accesso già presente nell'istanza nel 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.

Per risolvere manualmente il problema, seguire questa procedura:

  1. Controllare attentamente lo script di output.
  2. Esaminare il contenuto della sys.server_principals visualizzazione nell'istanza nel server B.
  3. Risolvere questi messaggi di errore come appropriato.

A partire da SQL Server 2005, il SID per un account di accesso viene usato per gestire l'accesso a livello di database. In alcuni casi, un account di accesso potrebbe avere SID diversi quando viene eseguito il mapping agli utenti in database diversi. Questo problema può verificarsi se i database vengono combinati manualmente da server diversi. In questi casi, l'account di accesso può accedere solo al database in cui il SID dell'entità di database corrisponde al SID nella sys.server_principals vista. Per risolvere questo problema, rimuovere manualmente l'utente del database con il SID non corrispondente usando l'istruzione DROP USER . Aggiungere quindi di nuovo l'utente con l'istruzione ed eseguirne il CREATE USER mapping all'account di accesso corretto (entità server).

Per altre informazioni e per distinguere i server dalle entità di database, vedere CREATE USER e CREATE LOGIN.

Riferimenti