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

Artikel
12/14/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.

Notiz

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

Weitere Informationen

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

Nachdem Sie eine Datenbank aus der Instanz von SQL Server auf Server A in die Instanz von SQL Server auf Server B verschoben haben, können sich Benutzer möglicherweise nicht 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, Fehler: 18456)

Dieses Problem tritt auf, da Sie die Anmeldeinformationen und die Kennwörter nicht von der Instanz von SQL Server auf Server A an die Instanz von SQL Server auf Server B übertragen haben.

Notiz

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

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

Methode 1: Zurücksetzen des Kennworts auf dem SQL Server-Zielcomputer (Server B).

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.

Notiz

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

Methode 2: Übertragen von Anmeldeinformationen und Kennwörtern an 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

Notiz

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

Überprüfen Sie die Informationen im folgenden Abschnitt "Hinweise ", bevor Sie mit den Implementierungsschritten auf dem Zielserver fortfahren.

Schritte auf dem Zielserver (Server B)

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

Hinweise

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 mithilfe des neuen Domänennamens in den CREATE LOGIN Anweisungen ersetzen. Die integrierten Anmeldungen, denen der Zugriff in der neuen Domäne gewährt wird, verfügen nicht über dieselbe SID wie die Anmeldungen in der ursprünglichen Domäne. 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. Es sei denn, ein Mitglied der sysadmin fixed-Serverrolle gewährt den Benutzern die erforderlichen Berechtigungen, die Benutzer können das Ausgabeskript nicht erstellen oder ausführen.
Die Schritte in diesem Artikel übertragen nicht die Standarddatenbankinformationen für eine bestimmte Anmeldung. Dies liegt daran, dass die Standarddatenbank möglicherweise nicht immer auf Server B vorhanden ist. Um die Standarddatenbank für eine Anmeldung 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 und Groß-/Kleinschreibung werden beachtet: Bei der Sortierreihenfolge von Server A kann die Groß-/Kleinschreibung nicht beachtet werden, und bei der Sortierreihenfolge von Server B wird 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 und Groß-/Kleinschreibung wird nicht beachtet: Bei der Sortierreihenfolge von Server A kann die Groß-/Kleinschreibung beachtet werden, und bei der Sortierreihenfolge von Server B wird die Groß-/Kleinschreibung nicht beachtet. In diesem Fall können sich Benutzer nicht mit den Anmeldeinformationen und den Kennwörtern anmelden, die Sie auf die Instanz auf Server B übertragen, es sei denn, eine der folgenden Bedingungen gilt:
  - Die ursprünglichen Kennwörter enthalten keine Buchstaben.
  - Alle Buchstaben in den ursprünglichen Kennwörtern sind Großbuchstaben.
- Groß-/Kleinschreibung wird auf beiden Servern beachtet: Bei der Sortierreihenfolge von Server A und Server B wird die Groß-/Kleinschreibung berücksichtigt, oder bei der Sortierreihenfolge von Server A und Server B wird die Groß-/Kleinschreibung nicht beachtet. In diesen Fällen treten keine Probleme auf.
Eine Anmeldung, die sich bereits in der Instanz auf Server B befindet, weist möglicherweise einen Namen auf, der mit einem Namen 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 15025, Ebene 16, Status 1, Zeile 1
Der Serverprinzipal MyLogin ist bereits vorhanden.

Ebenso kann eine Anmeldung, die sich bereits in der Instanz auf Server B befindet, über eine SID verfügen, 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. Überprüfen Sie den Inhalt der sys.server_principals Ansicht in der Instanz 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. Bei einer Anmeldung gibt es möglicherweise unterschiedliche SIDs in verschiedenen Datenbanken auf einem Server. 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 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)

Hinweise

References

Feedback

Zusätzliche Ressourcen