Aanmeldingen en wachtwoorden overbrengen tussen exemplaren van SQL Server

Van toepassing: Microsoft SQL Server 2005 Standard EditionMicrosoft SQL Server 2005 Workgroup EditionMicrosoft SQL Server 2005 Developer Edition

INLEIDING


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

Opmerking De exemplaren die mogelijk op dezelfde server of op verschillende servers en hun versies kunnen verschillen.

Voor meer informatie over het overdragen van de aanmeldingen en wachtwoorden tussen exemplaren van andere versies van SQL Server, klikt u op het volgende artikel in de Microsoft Knowledge Base:

246133 het overdragen van aanmeldingen en wachtwoorden tussen exemplaren van SQL Server met oudere versies van SQL Server

Meer informatie


In dit artikel zijn server A en server B verschillende servers.
 
Nadat u een database van het exemplaar van SQL Server op server A verplaatst naar het exemplaar van SQL Server op server B, gebruikers mogelijk niet aanmelden bij de database op de server bovendien B., gebruikers wordt het volgende foutbericht weergegeven:
Aanmelding mislukt voor gebruiker 'MyUser'. (Microsoft SQL Server, fout: 18456)
Dit probleem treedt op omdat u niet de aanmeldingen en wachtwoorden van het exemplaar van SQL Server op een server met het exemplaar van SQL Server op de server B. overdragen hebt

Om over te brengen van de aanmeldingen, gebruikt u een van de volgende methoden, afhankelijk van uw situatie.

Methode 1: Het wachtwoord op de doelcomputer van SQL Server (Server B) opnieuw instellen

Dit probleem oplossen door het wachtwoord in de SQL Server-computer opnieuw en vervolgens een script van de aanmelding.

Opmerking Het wachtwoord hash-algoritme wordt gebruikt wanneer u het wachtwoord opnieuw.

Methode 2: Aanmeldingen en wachtwoorden overbrengen naar de doelserver (Server A) met behulp van scripts die zijn gegenereerd op de bronserver (Server B)

Als u een logboek in een script dat een blanco wachtwoord maakt, als volgt te werk:
  1. Start SQL Server Management Studio op een server en vervolgens verbinding maken met het exemplaar van SQL Server waaruit u de database verplaatst.
  2. Open een nieuwe Query Editor-venster en voer het volgende script.
    USE master
    GO
    IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
    DROP PROCEDURE sp_hexadecimal
    GO
    CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
    AS
    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
    GO

    IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
    DROP PROCEDURE sp_help_revlogin
    GO
    CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
    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 @defaultdb sysname

    IF (@login_name IS NULL)
    DECLARE login_curs CURSOR FOR

    SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin 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'
    ELSE
    DECLARE login_curs CURSOR FOR


    SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin 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
    OPEN login_curs

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
    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
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group

    SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    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 + ']'

    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
    PRINT @tmpstr
    END

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
    END
    CLOSE login_curs
    DEALLOCATE login_curs
    RETURN 0
    GO


    Opmerking Dit script maakt twee opgeslagen procedures in de hoofddatabase . De procedures hebben de naam sp_hexadecimal en sp_help_revlogin.
  3. Voer de volgende instructie in dezelfde of een nieuwe query-venster:
    EXEC sp_help_revlogin
    Het script uitvoer de sp_help_revlogin opgeslagen procedure genereert wordt het aanmeldingsscript. Dit aanmeldingsscript wordt gemaakt van de aanmeldingen die de oorspronkelijke beveiligings-id (SID) en het oorspronkelijke wachtwoord hebben.

Stappen op de doelserver (Server B):

  1. Start SQL Server Management Studio op server B, en vervolgens verbinding maken met het exemplaar van SQL Server waarnaar u de database verplaatst.

    Belangrijk Voordat u met stap 2 verdergaat, lees de informatie in de sectie 'Opmerkingen' hieronder.
  2. Open een nieuwe Query Editor-venster en voer het script uitvoer die in stap 2 van de voorgaande procedure wordt gegenereerd.

Opmerkingen

Lees de volgende informatie voordat u het script uitvoer op het exemplaar op de server B:

  • Een wachtwoord kan opdelen in de volgende manieren:
    • VERSION_SHA1: deze hash met behulp van de SHA1-algoritme wordt gegenereerd en wordt gebruikt in SQL Server 2000 tot en met SQL Server 2008 R2.
    • VERSION_SHA2: deze hash met behulp van de 512 SHA2 algoritme wordt gegenereerd en wordt gebruikt in SQL Server 2012 en latere versies.
  • Lees zorgvuldig het script uitvoer. Als server A en server B bevinden zich in verschillende domeinen, moet u het script uitvoer wijzigen. Vervolgens hebt u de naam van het oorspronkelijke domein vervangen door de nieuwe domeinnaam in de instructies voor aanmelding maken. De geïntegreerde aanmeldingen die toegang in het nieuwe domein hoeft niet dezelfde SID als de aanmeldingen in het oorspronkelijke domein. Dus gebruikers meer van deze aanmeldingen. Voor meer informatie over het oplossen van deze zwevende gebruikers, klikt u op het volgende artikel in de Microsoft Knowledge Base:

    240872 het oplossen van problemen met toestemming wanneer u een database verplaatsen tussen servers met SQL Server

    Als server A en server B zich in hetzelfde domein bevinden, wordt dezelfde SID gebruikt. Gebruikers die waarschijnlijk niet worden zwevend.
  • De aanmeldingen worden gemaakt in het script uitvoer met behulp van het gecodeerde wachtwoord. Dit komt door het argument HASHED in de instructie-aanmelding maken. Dit argument geeft aan dat het wachtwoord dat is opgegeven na het wachtwoordargument al wordt opgedeeld.
  • Standaard kan alleen een lid van de vaste serverrol sysadmin een SELECT-instructie uitgevoerd vanuit de weergave sys.server_principals . Als lid van de sysadmin serverrol vaste verleent de benodigde machtigingen aan gebruikers, gebruikers kunnen maken of uitvoeren van het script uitvoer.
  • De standaard database-informatie voor een bepaalde aanmelding moeten u de stappen in dit artikel niet overbrengen. Dit komt omdat de standaarddatabase niet altijd op de server B. bestaat mogelijk De standaarddatabase-aanmeldingen gebruik definiëren met de instructie ALTER aanmelding door doorgeven in de aanmeldingsnaam en de standaarddatabase als argumenten.
  • Sorteervolgorden in bron-en doelserver:
    • Niet-hoofdlettergevoelige server A en server hoofdlettergevoelig B: de sorteervolgorde van een server mogelijk niet hoofdlettergevoelig en de sorteervolgorde van de server B mogelijk hoofdlettergevoelig. In dit geval moeten typen gebruikers de wachtwoorden in hoofdletters staan nadat u de aanmeldingen overbrengen en de wachtwoorden naar het exemplaar op server B.
    • Hoofdlettergevoelige server A en niet-hoofdlettergevoelige server B: De sorteervolgorde van een server hoofdlettergevoelig, en de sorteervolgorde van de server B kan zijn niet hoofdlettergevoelig. In dit geval gebruikers zich niet aanmelden met behulp van de aanmeldingen en wachtwoorden die u naar het exemplaar op de server B, overbrengt tenzij een van de volgende voorwaarden voldaan wordt:
      • De oorspronkelijke wachtwoorden bevatten geen letters.
      • Alle letters in de oorspronkelijke wachtwoorden zijn hoofdletters.
    • Dan niet hoofdlettergevoelig op beide servers: de sorteervolgorde van server A en server B mogelijk hoofdlettergevoelig of de sorteervolgorde van server A en server B kan zijn niet hoofdlettergevoelig. In deze gevallen ondervinden de gebruikers geen probleem.
  • Een aanmelding die al in het exemplaar op de server B kan een naam hebben die gelijk is aan een naam in het script uitvoer. In dit geval wordt het volgende foutbericht weergegeven wanneer u het script uitvoer op het exemplaar op de server B:
    Msg 15025, niveau 16 staat 1, regel 1
    De server die principal 'MyLogin' al bestaat.
    Een aanmelding die al in het exemplaar op de server B wordt mogelijk ook een beveiligings-id die hetzelfde is als een beveiligings-id in het script uitvoer. In dit geval wordt het volgende foutbericht weergegeven wanneer u het script uitvoer op het exemplaar op de server B:
    Msg 15433, niveau 16 staat 1, regel 1
    Opgegeven parameter sid wordt gebruikt.
    Daarom moet u het volgende doen:
    1. Lees zorgvuldig het script uitvoer.
    2. Bekijk de inhoud van de weergave van sys.server_principals in het exemplaar op de server B.
    3. Deze foutberichten worden weergegeven als het juiste adres.

      De SID voor een aanmelding wordt gebruikt in SQL Server 2005 database-toegang implementeren. Een aanmelding hebben verschillende beveiligings-id's in verschillende databases op een server. In dit geval wordt de aanmelding alleen toegang tot de database met de beveiligings-id die overeenkomt met de SID in de weergave van de sys.server_principals. Dit probleem kan optreden als de twee databases van verschillende servers worden gecombineerd. U lost dit probleem, de aanmelding handmatig te verwijderen uit de database met SID niet overeen met de instructie DROP USER. Vervolgens voegt u de aanmelding opnieuw met behulp van de instructie CREATE USER.
  • Als u probeert een nieuwe SQL Server 2012-aanmelding maken met behulp van een pre-SQL Server 2000 login script wordt uitgevoerd, wordt het volgende foutbericht weergegeven:

    Msg 15021, niveau 16 staat 2, regel 1
    Ongeldige waarde voor de parameter wachtwoord opgegeven. Geef een geldige parameterwaarde.

    Opmerking Deze fout wordt weergegeven in SQL Server 2012 vanwege de wachtwoordhash van 16 bytes die is opgegeven voor de aanmelding maken en VERANDEREN LOGIN instructies.

    Dit probleem oplossen op een server waarop SQL Server 2012, maak een account met een leeg wachtwoord. Voer hiervoor het volgende script:
    CREATE LOGIN [Test] WITH PASSWORD = '', SID = 0x90FD605DCEFAE14FAB4D5EB0BBA1AECC, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF
    Nadat u de aanmelding met een blanco wachtwoord maakt, kan de gebruiker het wachtwoord bij de volgende aanmelding wijzigen.

Methode 3: Log in met behulp van het wachtwoord voor SQL Server 2000

Opmerking Deze methode is alleen relevant als u SQL Server 2000 naar een meer recente ondersteunde versie van SQL server migreren wilt.

In dat geval vraagt de gebruiker zich aanmelden bij de server waarop SQL Server wordt uitgevoerd met behulp van de aanmelding voor SQL Server 2000.

Opmerking De hash van het wachtwoord wordt automatisch bijgewerkt wanneer de gebruiker zich aanmeldt met behulp van het wachtwoord voor SQL Server 2000.

Referenties


Ga naar het Oplossen van problemen met zwevende gebruikers Microsoft Developer Network (MSDN)-website voor meer informatie over het oplossen van problemen met zwevende gebruikers.

Ga naar de MSDN-website voor Aanmelding maken (Transact-SQL) voor meer informatie over de instructie-aanmelding maken.

Ga naar de MSDN-website voor Aanmelding wijzigen (Transact-SQL) voor meer informatie over de instructie ALTER-aanmelding.