Übertragen von Benutzernamen und Kennwörtern zwischen Instanzen von SQL Server

Artikel
09/06/2024

In diesem Artikel wird beschrieben, wie Sie die Benutzernamen und Kennwörter zwischen verschiedenen Instanzen von SQL Server übertragen, die unter Windows ausgeführt werden.

Ursprüngliche Produktversion: SQL Server
Ursprüngliche KB-Nummer: 918992, 246133

Einführung

In diesem Artikel wird beschrieben, wie Sie die Benutzernamen und Kennwörter zwischen verschiedenen Instanzen von Microsoft SQL Server übertragen.

Hinweis

Die Instanzen befinden sich möglicherweise auf demselben Server oder auf verschiedenen Servern, und ihre Versionen können sich unterscheiden.

Weitere Informationen

In diesem Artikel sind Server A und Server B unterschiedliche Server.

Nachdem Sie eine Datenbank vom instance von SQL Server auf Server A in die instance von SQL Server auf Server B verschoben haben, können sich Benutzer möglicherweise nicht mehr bei der Datenbank auf Server B anmelden. Darüber hinaus erhalten Benutzer möglicherweise die folgende Fehlermeldung:

Fehler bei der Anmeldung für den Benutzer „“. (Microsoft SQL Server, Error: 18456)

Dieses Problem tritt auf, weil Sie die Anmeldungen und Kennwörter nicht von der instance von SQL Server auf Server A an die instance von SQL Server auf Server B übertragen haben.

Hinweis

Die Fehlermeldung 18456 tritt auch aus anderen Gründen auf. Weitere Informationen zu diesen Ursachen und möglichen Lösungen finden Sie unter MSSQLSERVER_18456.

Um die Benutzernamen zu übertragen, verwenden Sie eine der folgenden Methoden, je nach Ihrer Situation.

Methode 1: Setzen Sie das Kennwort auf dem Zielcomputer SQL Server (Server B) zurück.

Um dieses Problem zu beheben, setzen Sie das Kennwort auf dem SQL Server-Computer zurück, und erstellen Sie dann ein Skript für die Anmeldung.

Hinweis

Der Kennworthashingalgorithmus wird verwendet, wenn Sie das Kennwort zurücksetzen.

Methode 2: Übertragen von Anmeldungen und Kennwörtern auf den Zielserver (Server B) mithilfe von Skripts, die auf dem Quellserver (Server A) generiert wurden.

Erstellen Sie gespeicherte Prozeduren, die dazu beitragen, erforderliche Skripts zum Übertragen von Benutzernamen und deren Kennwörtern zu generieren. Stellen Sie dazu eine Verbindung mit Server A mithilfe von SQL Server Management Studio (SSMS) oder einem anderen Clienttool her, und führen Sie das folgende Skript aus:

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

Hinweis

Dieses Skript erstellt zwei gespeicherte Prozeduren in der Masterdatenbank. Die Prozeduren werden sp_hexadecimal und sp_help_revlogin genannt.

Wählen Sie im SSMS-Abfrage-Editor die Option Ergebnisse als Text aus.
Führen Sie die folgende Anweisung in demselben oder einem neuen Abfragefenster aus:
```
EXEC sp_help_revlogin
```
Das Ausgabeskript, das von der sp_help_revlogin gespeicherten Prozedur generiert wird, ist das Anmeldeskript. Dieses Anmeldeskript erstellt die Anmeldungen mit der ursprünglichen Sicherheits-ID (SID) und dem ursprünglichen Kennwort.

Wichtig

Lesen Sie die Informationen im folgenden Abschnitt Hinweise , bevor Sie mit der Implementierung der Schritte auf dem Zielserver fortfahren.

Schritte auf dem Zielserver (Server B)

Stellen Sie mithilfe eines beliebigen Clienttools (z. B. SSMS) eine Verbindung mit Server B her, und führen Sie dann das in Schritt 4 (Ausgabe von sp_helprevlogin) generierte Skript von Server A aus.

HinwBemerkungeneise

Lesen Sie die folgenden Informationen, bevor Sie das Ausgabeskript auf der Instanz auf Server B ausführen:

Ein Kennwort kann auf folgende Weise gehasht werden:
- VERSION_SHA1: Dieser Hash wird mithilfe des SHA1-Algorithmus generiert und in SQL Server 2000 bis SQL Server 2008 R2 verwendet.
- VERSION_SHA2: Dieser Hash wird mithilfe des SHA2 512-Algorithmus generiert und in SQL Server 2012 und höheren Versionen verwendet.
Überprüfen Sie das Ausgabeskript sorgfältig. Wenn sich Server A und Server B in unterschiedlichen Domänen befinden, müssen Sie das Ausgabeskript ändern. Anschließend müssen Sie den ursprünglichen Domänennamen ersetzen, indem Sie den neuen Domänennamen in den CREATE LOGIN Anweisungen verwenden. Die integrierten Anmeldungen, denen Zugriff in der neuen Domäne gewährt wird, weisen nicht dieselbe SID wie die Anmeldungen in der ursprünglichen Domäne auf. Daher sind Benutzende von diesen Benutzernamen verwaist. Weitere Informationen zum Beheben dieser verwaisten Benutzer finden Sie unter Problembehandlung für verwaiste Benutzer (SQL Server) und ALTER USER.
Wenn sich Server A und Server B in derselben Domäne befinden, wird dieselbe SID verwendet. Daher ist es unwahrscheinlich, dass Benutzende verwaist sind.
Im Ausgabeskript werden die Benutzernamen mithilfe des verschlüsselten Kennworts erstellt. Dies liegt am HASHED-Argument in der CREATE LOGIN-Anweisung. Dieses Argument gibt an, dass das Kennwort, das nach dem Argument PASSWORD eingegeben wird, bereits gehasht ist.
Standardmäßig kann nur ein Mitglied der festen Serverrolle „sysadmin“ eine SELECT-Anweisung aus der sys.server_principals-Ansicht ausführen. Wenn den Benutzern nicht ein Mitglied der festen Serverrolle sysadmin die erforderlichen Berechtigungen erteilt, können die Benutzer das Ausgabeskript nicht erstellen oder ausführen.
Die Schritte in diesem Artikel übertragen nicht die Standarddatenbankinformationen für einen bestimmten Anmeldenamen. Dies liegt daran, dass die Standarddatenbank möglicherweise nicht immer auf Server B vorhanden ist. Um die Standarddatenbank für einen Anmeldenamen zu definieren, verwenden Sie die ALTER LOGIN -Anweisung, indem Sie den Anmeldenamen und die Standarddatenbank als Argumente übergeben.
Sortierreihenfolgen auf Quell- und Zielserver:
- Server A ohne Berücksichtigung der Groß-/Kleinschreibung und Server B: Bei der Sortierreihenfolge von Server A kann die Groß-/Kleinschreibung nicht beachtet werden, und bei der Sortierreihenfolge von Server B wird möglicherweise die Groß-/Kleinschreibung beachtet. In diesem Fall müssen Benutzer die Kennwörter in Großbuchstaben eingeben, nachdem Sie die Anmeldungen und Kennwörter in die Instanz auf Server B übertragen haben.
- Server A mit Beachtung der Groß-/Kleinschreibung und Server B ohne Berücksichtigung der Groß-/Kleinschreibung: Bei der Sortierreihenfolge von Server A kann die Groß-/Kleinschreibung beachtet werden, und bei der Sortierreihenfolge von Server B wird möglicherweise die Groß-/Kleinschreibung nicht beachtet. In diesem Fall können sich Benutzer nicht mit den Anmeldungen und Kennwörtern anmelden, die Sie an den instance auf Server B übertragen, es sei denn, eine der folgenden Bedingungen ist zutreffen:
  - Die ursprünglichen Kennwörter enthalten keine Buchstaben.
  - Alle Buchstaben in den ursprünglichen Kennwörtern sind Großbuchstaben.
- Berücksichtigung der Groß- und Kleinschreibung auf beiden Servern: Bei der Sortierreihenfolge von Server A und Server B kann die Groß-/Kleinschreibung beachtet werden, oder bei der Sortierreihenfolge von Server A und Server B wird die Groß-/Kleinschreibung nicht beachtet. In diesen Fällen tritt für die Benutzer kein Problem auf.
Eine Anmeldung, die sich bereits im instance auf Server B befindet, hat möglicherweise einen Namen, der mit dem Namen im Ausgabeskript übereinstimmt. In diesem Fall erhalten Sie die folgende Fehlermeldung, wenn Sie das Ausgabeskript in der Instanz auf Server B ausführen:

Meldung 15025, Ebene 16, Status 1, Zeile 1
Der Serverprinzipal MyLogin ist bereits vorhanden.

Ebenso kann eine Anmeldung, die sich bereits im instance auf Server B befindet, eine SID aufweisen, die mit einer SID im Ausgabeskript identisch ist. In diesem Fall erhalten Sie die folgende Fehlermeldung, wenn Sie das Ausgabeskript in der Instanz auf Server B ausführen:

Meldung 15433, Ebene 16, Status 1, Zeile 1 Der Parameter „sid“ wird verwendet.

Hier sollten Sie Folgendes tun:
1. Überprüfen Sie das Ausgabeskript sorgfältig.
2. Untersuchen Sie den Inhalt der sys.server_principals Ansicht im instance auf Server B.
3. Beheben Sie gegebenenfalls diese Fehlermeldungen.
  
  In SQL Server 2005 wird die SID für eine Anmeldung verwendet, um den Zugriff auf Datenbankebene zu implementieren. Eine Anmeldung kann unterschiedliche SIDs in verschiedenen Datenbanken auf einem Server aufweisen. In diesem Fall kann die Anmeldung nur auf die Datenbank mit der SID zugreifen, die mit der SID in der sys.server_principals-Ansicht übereinstimmt. Dieses Problem kann auftreten, wenn die beiden Datenbanken von verschiedenen Servern aus kombiniert werden. Um dieses Problem zu beheben, entfernen Sie die Anmeldung, bei der die SID nicht übereinstimmt, manuell aus der Datenbank, indem Sie die „DROP USER“-Anweisung verwenden. Fügen Sie dann die Anmeldung erneut mit der CREATE USER-Anweisung hinzu.

Freigeben über

Übertragen von Benutzernamen und Kennwörtern zwischen Instanzen von SQL Server

Einführung

Weitere Informationen

Schritte auf dem Zielserver (Server B)

HinwBemerkungeneise

References

Feedback

Zusätzliche Ressourcen