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

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 können sich auf demselben Server oder auf verschiedenen Servern befinden, und ihre Versionen können sich unterscheiden.

Weitere Informationen

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

Nachdem Sie eine Datenbank von der Instanz von SQL Server auf Server A in die Instanz von SQL Server auf Server B verschoben haben, können sich Benutzende möglicherweise nicht bei der Datenbank auf Server B anmelden. Darüber hinaus erhalten Benutzende 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 Benutzernamen und Kennwörter nicht von der Instanz von SQL Server auf Server A auf die Instanz 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: 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.

    Hinweis

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

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

    1. 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 ('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'
                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
      

      Hinweis

      Dieses Skript erstellt zwei gespeicherte Prozeduren in der Masterdatenbank. Die Prozeduren haben die Namen sp_hexadecimal und sp_help_revlogin.

    2. Wählen Sie im SSMS-Abfrage-Editor die Option Ergebnisse als Text aus.

    3. Führen Sie die folgende Anweisung in demselben oder einem neuen Abfragefenster aus:

      EXEC sp_help_revlogin
      
    4. 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 Abschnitt Hinweise weiter unten, bevor Sie mit der Implementierung der Schritte auf dem Zielserver fortfahren.

Schritte auf dem Zielserver (Server B)

Stellen Sie eine Verbindung mit Server B mit einem beliebigen Clienttool (z. B. SSMS) her, und führen Sie dann das in Schritt 4 (Ausgabe von sp_helprevlogin) von Server A generierte Skript 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 durch den neuen Domänennamen in den CREATE LOGIN-Anweisungen ersetzen. Die integrierten Benutzernamen, denen der Zugriff in der neuen Domäne gewährt wird, verfügen nicht über die gleiche SID wie die Benutzernamen 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. Solange ein Mitglied der festen Serverrolle „sysadmin“ Benutzern nicht die erforderlichen Berechtigungen erteilt, können diese das Ausgabeskript weder erstellen noch ausführen.

  • Die in diesem Artikel beschriebenen Schritte übertragen nicht die Standarddatenbankinformationen für eine bestimmte Anmeldung. Dies liegt daran, dass die Standarddatenbank nicht immer auf Server B verfügbar ist. Um die Standarddatenbank für eine Anmeldung zu definieren, verwenden Sie die Anweisung ALTER LOGIN, 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 mit Berücksichtigung der Groß-/Kleinschreibung: Die Sortierreihenfolge von Server A ist möglicherweise ohne Berücksichtigung der Groß-/Kleinschreibung und die Sortierreihenfolge von Server B ist möglicherweise mit Berücksichtigung der Groß-/Kleinschreibung. 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 ohne Berücksichtigung der Groß- und Kleinschreibung und Server B mit Berücksichtigung der Groß- und Kleinschreibung: Die Sortierreihenfolge von Server A ist möglicherweise ohne Berücksichtigung der Groß- und Kleinschreibung und die Sortierreihenfolge von Server B ist möglicherweise mit Berücksichtigung der Groß- und Kleinschreibung. In diesem Fall können sich Benutzer nicht mit den Anmeldungen und Kennwörtern anmelden, die Sie in die Instanz auf Server B übertragen haben, es sei denn, eine der folgenden Bedingungen wird erfüllt:

      • Die ursprünglichen Kennwörter enthalten keine Buchstaben.
      • Alle Buchstaben in den ursprünglichen Kennwörtern sind Großbuchstaben.
    • Groß- und Kleinschreibung auf beiden Servern: Die Sortierreihenfolge sowohl auf Server A als auch auf Server B kann Groß- und Kleinschreibung beachten, oder die Sortierreihenfolge sowohl auf Server A als auch auf Server B kann Groß- und Kleinschreibung nicht beachten. In diesen Fällen treten für Benutzer keine Probleme auf.

  • Eine Anmeldung, die bereits in der Instanz auf Server B vorhanden ist, kann einen Namen haben, 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 bereits in der Instanz auf Server B vorhanden ist, eine SID haben, 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. Eine Anmeldung kann unterschiedliche SIDs in verschiedenen Datenbanken eines Servers 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 zusammengefügt 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.

References