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

    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'
                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, 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 werden sp_hexadecimal und sp_help_revlogin genannt.

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

References