Trasferire account di accesso e password tra istanze di SQL Server

Articolo
09/06/2024

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).

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

Nota

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

Nell'editor di query SSMS, selezionare l'opzione Risultati in formato testo.
Eseguire l'istruzione seguente nella stessa finestra di query oppure in una nuova:
```
EXEC sp_help_revlogin
```
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.

Condividi tramite

Trasferire account di accesso e password tra istanze di SQL Server

Introduzione

Altre informazioni

Procedure nel server di destinazione (Server B)

Osservazioni

Riferimenti

Commenti e suggerimenti

Risorse aggiuntive