Aanmeldingen en wachtwoorden overdragen tussen exemplaren van SQL Server

Artikel
10/16/2023

In dit artikel wordt beschreven hoe u de aanmeldingen en wachtwoorden overdraagt tussen verschillende exemplaren van SQL Server die worden uitgevoerd in Windows.

Originele productversie: SQL Server
Origineel KB-nummer: 918992, 246133

Inleiding

In dit artikel wordt beschreven hoe u de aanmeldingen en wachtwoorden overdraagt tussen verschillende exemplaren van Microsoft SQL Server.

Opmerking

De exemplaren kunnen zich op dezelfde server of op verschillende servers bevinden en hun versies kunnen verschillen.

Meer informatie

In dit artikel zijn server A en server B verschillende servers.

Nadat u een database hebt verplaatst van het exemplaar van SQL Server op server A naar het exemplaar van SQL Server op server B, kunnen gebruikers zich mogelijk niet aanmelden bij de database op server B. Daarnaast kunnen gebruikers het volgende foutbericht ontvangen:

Aanmelden is mislukt voor gebruiker 'MyUser'. (Microsoft SQL-server, fout: 18456)

Dit probleem treedt op omdat u de aanmeldingen en wachtwoorden niet hebt overgedragen van het exemplaar van SQL Server op server A naar het exemplaar van SQL Server op server B.

Opmerking

Het foutbericht 18456 treedt ook om andere redenen op. Zie MSSQLSERVER_18456 voor meer informatie over deze oorzaken en mogelijke oplossingen.

Gebruik voor het overdragen van de aanmeldingen een van de volgende methoden, afhankelijk van jouw situatie.

Methode 1: Stel het wachtwoord op de doelcomputer SQL Server computer (Server B) opnieuw in.

U kunt dit probleem oplossen door het wachtwoord opnieuw in te stellen op de SQL Server-computer en vervolgens de aanmelding te scripten.

Opmerking

Het algoritme voor wachtwoord-hashing wordt gebruikt wanneer u het wachtwoord opnieuw instelt.

Methode 2: Breng aanmeldingen en wachtwoorden over naar de doelserver (Server B) met behulp van scripts die zijn gegenereerd op de bronserver (Server A).

Maak opgeslagen procedures waarmee u de benodigde scripts kunt genereren om aanmeldingen en hun wachtwoorden over te dragen. Hiervoor maakt u verbinding met Server A met behulp van SQL Server Management Studio (SSMS) of een ander clienthulpprogramma en voert u het volgende script uit:

  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

Opmerking

Met dit script worden twee opgeslagen procedures in de hoofddatabase gemaakt. De procedures heten sp_hexadecimal en sp_help_revlogin.

Selecteer in de SSMS-queryeditor de optie Resultaten naar tekst.
Voer de volgende instructie uit in hetzelfde of een nieuw queryvenster:
```
EXEC sp_help_revlogin
```
Het uitvoerscript dat door de sp_help_revlogin opgeslagen procedure wordt gegenereerd, is het aanmeldingsscript. Met dit aanmeldingsscript worden de aanmeldingen gemaakt met de oorspronkelijke beveiligings-id (SID) en het oorspronkelijke wachtwoord.

Belangrijk

Controleer de informatie in de volgende sectie Opmerkingen voordat u doorgaat met het implementeren van stappen op de doelserver.

Stappen op de doelserver (Server B)

Maak verbinding met Server B met behulp van een clienthulpprogramma (zoals SSMS) en voer vervolgens het script uit dat is gegenereerd in stap 4 (uitvoer van sp_helprevlogin) van Server A.

Opmerkingen

Lees de volgende informatie voordat u het uitvoerscript uitvoert op het exemplaar op server B:

Een wachtwoord kan op de volgende manieren worden gehasht:
- VERSION_SHA1: deze hash wordt gegenereerd met behulp van het SHA1-algoritme en wordt gebruikt in SQL Server 2000 tot en met SQL Server 2008 R2.
- VERSION_SHA2: deze hash wordt gegenereerd met behulp van het algoritme SHA2 512 en wordt gebruikt in SQL Server 2012 en latere versies.
Lees het uitvoerscript zorgvuldig. Als server A en server B zich in verschillende domeinen bevinden, moet u het uitvoerscript wijzigen. Vervolgens moet u de oorspronkelijke domeinnaam vervangen door de nieuwe domeinnaam in de CREATE LOGIN instructies te gebruiken. De geïntegreerde aanmeldingen die toegang krijgen in het nieuwe domein, hebben niet dezelfde SID als de aanmeldingen in het oorspronkelijke domein. Daarom zijn gebruikers zwevende gebruikers van deze aanmeldingen. Zie Problemen met zwevende gebruikers oplossen (SQL Server) en ALTER USER voor meer informatie over het oplossen van deze zwevende gebruikers.
Als server A en server B zich in hetzelfde domein bevinden, wordt dezelfde SID gebruikt. Daarom is het onwaarschijnlijk dat gebruikers zwevend zijn.
In het uitvoerscript worden de aanmeldingen gemaakt met behulp van het versleutelde wachtwoord. Dit komt door het argument HASHED in de CREATE LOGIN instructie. Dit argument geeft aan dat het wachtwoord dat is ingevoerd na het argument PASSWORD al is gehasht.
Standaard kan alleen een lid van de vaste serverfunctie sysadmin een SELECT instructie uitvoeren vanuit de sys.server_principals weergave. Tenzij een lid van de vaste serverrol sysadmin de benodigde machtigingen aan de gebruikers verleent, kunnen de gebruikers het uitvoerscript niet maken of uitvoeren.
Met de stappen in dit artikel worden de standaarddatabasegegevens voor een bepaalde aanmelding niet overgedragen. Dit komt doordat de standaarddatabase mogelijk niet altijd bestaat op server B. Als u de standaarddatabase voor een aanmelding wilt definiëren, gebruikt u de ALTER LOGIN -instructie door de aanmeldingsnaam en de standaarddatabase als argumenten door te geven.
Sorteervolgorden op bron- en doelservers:
- Niet-hoofdlettergevoelige server A en hoofdlettergevoelige server B: De sorteervolgorde van server A is mogelijk niet hoofdlettergevoelig en de sorteervolgorde van server B kan hoofdlettergevoelig zijn. In dit geval moeten gebruikers de wachtwoorden in hoofdletters typen nadat u de aanmeldingen en de wachtwoorden hebt overgedragen naar het exemplaar op server B.
- Hoofdlettergevoelige server A en niet-hoofdlettergevoelige server B: De sorteervolgorde van server A kan hoofdlettergevoelig zijn en de sorteervolgorde van server B is mogelijk niet hoofdlettergevoelig. In dit geval kunnen gebruikers zich niet aanmelden met behulp van de aanmeldingen en de wachtwoorden die u naar het exemplaar op server B overbrengt, tenzij aan een van de volgende voorwaarden is voldaan:
  - De oorspronkelijke wachtwoorden bevatten geen letters.
  - Alle letters in de oorspronkelijke wachtwoorden zijn hoofdletters.
- Hoofdlettergevoelig of niet-hoofdlettergevoelig op beide servers: de sorteervolgorde van server A en server B kan hoofdlettergevoelig zijn of de sorteervolgorde van zowel server A als server B is mogelijk niet hoofdlettergevoelig. In deze gevallen ondervinden de gebruikers geen probleem.
Een aanmelding die zich al in het exemplaar op server B bevindt, kan een naam hebben die hetzelfde is als een naam in het uitvoerscript. In dit geval ontvangt u het volgende foutbericht wanneer u het uitvoerscript uitvoert op het exemplaar op server B:

Msg 15025, Level 16, State 1, Line 1
De server-principal 'MyLogin' bestaat al.

Op dezelfde manier kan een aanmelding die zich al in het exemplaar op server B bevindt, een SID hebben die hetzelfde is als een SID in het uitvoerscript. In dit geval ontvangt u het volgende foutbericht wanneer u het uitvoerscript uitvoert op het exemplaar op server B:

Msg 15433, Level 16, State 1, Line 1 geleverde parameter sid is in gebruik.

Daarom dient u het volgende te doen:
1. Lees het uitvoerscript zorgvuldig.
2. Controleer de inhoud van de sys.server_principals weergave in het exemplaar op server B.
3. Los deze foutberichten zoals nodig op.
  
  In SQL Server 2005 wordt de SID voor een aanmelding gebruikt om toegang op databaseniveau te implementeren. Een aanmelding kan verschillende SID's hebben in verschillende databases op een server. In dit geval heeft de aanmelding alleen toegang tot de database met de SID die overeenkomt met de SID in de sys.server_principals weergave. Dit probleem kan optreden als de twee databases worden gecombineerd vanaf verschillende servers. U kunt dit probleem oplossen door de aanmelding handmatig te verwijderen uit de database met een SID die niet overeenkomt met de DROP USER-instructie. Voeg vervolgens de aanmelding opnieuw toe met behulp van de CREATE USER instructie.

Aanmeldingen en wachtwoorden overdragen tussen exemplaren van SQL Server

Inleiding

Meer informatie

Stappen op de doelserver (Server B)

Opmerkingen

Verwijzingen

Feedback

Feedback

Aanvullende resources