Hur du överför inloggningsnamn och lösenord mellan instanser av SQL Server

Gäller för: Microsoft SQL Server 2005 Standard EditionMicrosoft SQL Server 2005 Workgroup EditionMicrosoft SQL Server 2005 Developer Edition

Introduktion


Den här artikeln beskrivs hur du överför inloggningsnamn och lösenord mellan olika instanser av Microsoft SQL Server.

Obs! Instanserna som kan finnas på samma server eller på olika servrar och deras versioner kan variera.

Mer information om hur du överför inloggningsnamn och lösenord mellan olika instanser av andra versioner av SQL Server, klickar du på följande artikelnummer och läser artikeln i Microsoft Knowledge Base:

246133 hur du överför inloggningsnamn och lösenord mellan instanser av SQL Server som kör äldre versioner av SQL Server

Mer Information


I den här artikeln är server A och server B olika servrar.
 
När du flyttar en databas från en instans av SQL Server på server A till instansen av SQL Server på server B användare kan inte logga in på databasen på server B. Dessutom, visas kanske följande felmeddelande:
Inloggning misslyckades för användare 'MyUser'. (Microsoft SQL Server, fel: 18456)
Det här problemet uppstår eftersom du inte överföra inloggningar och lösenord från SQL Server-instansen på server A till instansen av SQL Server på server B.

Om du vill överföra inloggningar, använder du någon av följande metoder som passar din situation.

Metod 1: Återställa lösenordet på måldatorn för SQL Server (Server B)

Att lösa problemet, återställa lösenord i SQL Server-datorn och sedan skript i inloggningen.

Obs! Hash-algoritmen som lösenord används när du återställer lösenordet.

Metod 2: Överför inloggningsnamn och lösenord till målservern (Server A) med hjälp av skript skapas på källservern (Server B)

Om du vill skapa en logg i skriptet som har ett tomt lösenord, så här:
  1. Starta SQL Server Management Studio och Anslut till instansen av SQL Server som du har flyttat databasen på server A.
  2. Öppna ett nytt fönster i fråga Editor och kör du följande skript.
    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


    Obs! Det här skriptet skapar två lagrade procedurer i huvuddatabasen . Förfaranden som heter sp_hexadecimal och sp_help_revlogin.
  3. Kör följande uttryck i samma eller ett nytt fönster i fråga:
    EXEC sp_help_revlogin
    Utdata-skript som genererar sp_help_revlogin lagrade proceduren är inloggningsskriptet. Den här inloggningsskriptet skapar inloggningar som har ursprungliga säkerhets-ID (SID) och det ursprungliga lösenordet.

Steg på målservern (Server B):

  1. Starta SQL Server Management Studio och Anslut till instansen av SQL Server som du har flyttat databasen på server B.

    Viktigt Granska informationen i avsnittet ”Anmärkningar” innan du går vidare till steg 2.
  2. Öppna ett nytt fönster i fråga Editor och sedan köra skriptet utdata som genereras i steg 2 i föregående procedur.

Anmärkning

Läs följande information innan du kör skriptet utdata på instansen på servern B:

  • Ett lösenord kan vara hashas på följande sätt:
    • VERSION_SHA1: den här hash-värde genereras med hjälp av algoritmen SHA1 och används i SQL Server 2000 med SQL Server 2008 R2.
    • VERSION_SHA2: den här hash genereras med hjälp av algoritmen SHA2 512 och används i SQL Server 2012 och senare versioner.
  • Granska utdata skriptet noggrant. Du måste ändra skriptet utdata om server A och server B finns i olika domäner. Sedan måste du ersätta namnet på ursprungliga domänen med hjälp av det nya domännamnet i Skapa inloggning rapporterna. De integrerade inloggningar som beviljas åtkomst till den nya domänen har inte samma SID som inloggningar i den ursprungliga domänen. Användare är därför överblivna från dessa inloggningar. För mer information om hur du löser dessa överblivna användare klickar du på följande artikelnummer och läser artikeln i Microsoft Knowledge Base:

    240872 hur du löser problem med behörighet när du flyttar en databas mellan servrar som kör SQL Server

    Om server A och server B finns i samma domän, används samma SID. Användare är därför osannolikt att vara övergivna.
  • I skriptet utdata skapas inloggningar med det krypterade lösenordet. Detta beror på argumentet HASHED i uttrycket Skapa inloggning. Det här argumentet anger att lösenordet anges efter argumentet lösenord hashas redan.
  • Som standard kör en medlem av den fasta serverrollen sysadmin ett SELECT-kommando från den sys.server_principals vyn. Om en medlem av sysadmin fasta serverrollen ger behörighet till användare, användare inte kan skapa eller köra skript för utdata.
  • Åtgärderna i denna artikel överför inte databas standardinformation för en viss inloggning. Detta beror på att standarddatabasen som inte kanske alltid finns på server B. Om du vill definiera standarddatabasen för en inloggning, använder du programsatsen inloggning ALTER genom överföring i inloggningsnamn och standarddatabasen som argument.
  • Sorteringsordningar på käll- och målservrar:
    • Skiftlägesokänslig server A och server skiftlägeskänsliga B: sorteringsordningen på server A kan vara skiftlägeskänsliga och sorteringsordningen för server B kan vara skiftlägeskänsliga. I det här fallet måste användare ange lösenord i versaler när du överför inloggningar och lösenord till instansen på server B.
    • Skiftlägeskänslig server A och server skiftlägesoberoende B: Sorteringsordningen för server A kan vara skiftlägeskänsliga och sorteringsordningen för server B kan vara skiftlägeskänsliga. I detta fall kan logga användarna inte in med inloggningsnamn och lösenord som du överför till instansen på server B, såvida inte något av följande villkor är uppfyllt:
      • De ursprungliga lösenord innehålla några bokstäver.
      • Alla bokstäver i de ursprungliga lösenord är versaler.
    • Skiftlägeskänslig eller inte på båda servrarna: sorteringsordningen för både server A och server B kan vara skiftlägeskänsliga eller sorteringsordningen för både server A och server B kan vara skiftlägeskänsliga. I dessa fall uppstår användare inte problemet.
  • En inloggning som redan instans på server B kan ha ett namn som är detsamma som ett namn i skriptet utdata. I det här fallet visas följande felmeddelande när du kör skriptet utdata på instansen på servern B:
    Msg 15025, nivå 16, tillstånd 1, rad 1
    Den server som huvudsakliga 'MyLogin' finns redan.
    På samma sätt kan en inloggning som redan är i förekomsten på server B ha en SID som är detsamma som en SID i skriptet utdata. I det här fallet visas följande felmeddelande när du kör skriptet utdata på instansen på servern B:
    Msg 15433, nivå 16, tillstånd 1, rad 1
    Angivna parametern sid används.
    Därför måste du göra följande:
    1. Granska utdata skriptet noggrant.
    2. Granska innehållet i den sys.server_principals vyn i instansen server B.
    3. Behandla dessa felmeddelanden vid behov.

      I SQL Server 2005 används SID för en inloggning för att implementera behörighet databas. En inloggning kan ha olika SID i olika databaser på en server. I det här fallet åt inloggningen bara databasen med SID som matchar SID i vyn sys.server_principals. Det här problemet kan uppstå om kombineras de två databaserna från olika servrar. Lös problemet genom att manuellt ta bort inloggningen från databasen med SID överensstämmer inte med uttrycket DROP USER. Sedan lägger du till inloggningen igen med uttrycket CREATE USER.
  • Om du försöker skapa en ny inloggning på SQL Server 2012 med hjälp av en pre-SQL Server 2000-inloggning som är skript visas följande felmeddelande:

    Msg 15021, nivå 16, tillstånd 2, rad 1
    Felaktigt värde angavs för parametern lösenord. Ange ett giltigt parametervärde.

    Obs! Du kan få detta felmeddelande i SQL Server 2012 på 16 byte lösenordshashen som tillhandahålls för skapa inloggning och ändra inloggnings-satser.

    Skapa en inloggning med ett tomt lösenord om du vill lösa problemet på en server som kör SQL Server 2012. Det gör du genom att köra följande skript:
    CREATE LOGIN [Test] WITH PASSWORD = '', SID = 0x90FD605DCEFAE14FAB4D5EB0BBA1AECC, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF
    När du skapar en inloggning med ett tomt lösenord, kan användaren ändra lösenordet vid nästa inloggningsförsök.

Metod 3: Logga in med lösenordet för pre-SQL Server 2000

Obs! Den här metoden är endast relevant om du migrerar SQL Server 2000 till en nyare version av SQLServer som stöds.

Be användaren att logga in på servern som kör SQL Server med hjälp av pre-SQL Server 2000-inloggningen i den här situationen.

Obs! Lösenord hash uppdateras automatiskt när användaren loggar in med hjälp av pre-SQL Server 2000-lösenordet.

Referenser


Mer information om hur du felsöker överblivna användare går du till webbplatsen Microsoft Developer Network (MSDN) Felsöka överblivna användare .

Mer information om uttrycket Skapa inloggning går du till webbplatsen MSDN Skapa inloggning (Transact-SQL) .

Mer information om uttrycket ALTER inloggning går du till webbplatsen MSDN Ändra inloggning (Transact-SQL) .