Överföra inloggningar och lösenord mellan instanser av SQL Server

Ursprunglig produktversion: SQL Server
Ursprungligt KB-nummer: 918992, 246133

Sammanfattning

Den här artikeln visar hur du överför SQL Server inloggningar och lösenord mellan instanser av Microsoft SQL Server som körs på Windows. Använd dessa procedurer under SQL Server migrering, återställning eller scenarier med hög tillgänglighet för att hålla användarautentiseringen intakt och för att undvika överblivna databasanvändare på målinstansen. Käll- och målinstanserna kan finnas på samma server eller på olika servrar och deras versioner kan skilja sig åt.

Varför överföra inloggningar mellan SQL Server instanser

När du flyttar en databas till en ny server, till exempel under en migrering eller återställning, flyttas databasanvändare med databasen, men matchande inloggningar på servernivå kanske inte finns på den nya instansen. Den matchningsfelet ger överblivna användare. Överföring av inloggningar och lösenord håller användarautentiseringen intakt och förhindrar inloggningsstörningar efter en databasflytt.

När du har flyttat en databas från en SQL Server-instans på server A till en SQL Server-instans på server B kanske användarna inte kan logga in på databasservern på server B. Dessutom kan användarna få följande felmeddelande:

Inloggningen misslyckades för användaren 'MyUser'. (Microsoft SQL Server, fel: 18456)

Det här problemet beror på att inloggningarna från SQL Server-instansen på server A inte finns i SQL Server-instansen på server B.

Fel 18456 kan också inträffa av flera andra orsaker. Mer information om de olika orsakerna och deras lösningar finns i MSSQLSERVER_18456.

Metoder för att överföra inloggningar mellan SQL Server instanser

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

Generera inloggningsskript i SSMS och återställ lösenord på målet

Du kan generera inloggningsskript i SQL Server Management Studio (SSMS) med hjälp av alternativet Generera skript för en databas.

Följ dessa steg för att generera skript via SSMS på källservern och manuellt återställa lösenord för SQL Server-inloggningar på målservern:

1. Anslut till server A som är värd för SQL Server-källan.

2. Expandera noden Databaser.

3. Välj och håll ned (eller högerklicka på) en användardatabas och välj sedan Uppgifter>Generera skript.

4. Sidan Introduktion öppnas. Välj Nästa för att öppna sidan Välj objekt . Välj Skripta hela databasen och alla databasobjekt.

5. Välj Nästa för att öppna sidan Ange skriptalternativ .

6. Välj knappen Avancerat för inloggningsalternativ för skript.

7. I listan Avancerat letar du reda på Skriptinloggningar, anger alternativet till Sant och väljer OK.

8. Gå tillbaka till Ange skriptalternativ under Välj hur skript ska sparas och välj Öppna i nytt frågefönster.

9. Välj Nästa två gånger och välj sedan Slutför.

10. Leta reda på avsnittet i skriptet som innehåller inloggningar. Det genererade skriptet innehåller vanligtvis text med följande kommentar i början av det här avsnittet:

    /* For security reasons the login is created disabled and with a random password. */

    Kommentar

    Den här kommentaren anger att SQL Server-autentiseringsinloggningar genereras med ett slumpmässigt lösenord och inaktiveras som standard. Du måste återställa lösenordet och återaktivera dessa inloggningar på målservern.

11. Använd inloggningsskriptet från det större genererade skriptet på sql-målservern.

12. För alla SQL Server-autentiseringsinloggningar återställer du lösenordet på SQL Server-målet och återaktiverar dessa inloggningar.

Överföra inloggningar och lösenord med hjälp av sp_help_revlogin

1. Skapa lagrade procedurer som hjälper dig att generera nödvändiga skript för att överföra inloggningar och deras lösenord. Det gör du genom att ansluta till Server A med hjälp av SQL Server Management Studio (SSMS) eller något annat klientverktyg och köra följande skript:

   USE [master]
   GO
   IF OBJECT_ID('dbo.sp_hexadecimal') IS NOT NULL
       DROP PROCEDURE dbo.sp_hexadecimal
   GO
   CREATE PROCEDURE dbo.sp_hexadecimal
       @binvalue [varbinary](256)
       ,@hexvalue [nvarchar] (514) OUTPUT
   AS
   BEGIN
       DECLARE @i [smallint]
       DECLARE @length [smallint]
       DECLARE @hexstring [nchar](16)
       SELECT @hexvalue = N'0x'
       SELECT @i = 1
       SELECT @length = DATALENGTH(@binvalue)
       SELECT @hexstring = N'0123456789ABCDEF'
       WHILE (@i < =  @length)
       BEGIN
           DECLARE @tempint   [smallint]
           DECLARE @firstint  [smallint]
           DECLARE @secondint [smallint]
           SELECT @tempint = CONVERT([smallint], SUBSTRING(@binvalue, @i, 1))
           SELECT @firstint = FLOOR(@tempint / 16)
           SELECT @secondint = @tempint - (@firstint * 16)
           SELECT @hexvalue = @hexvalue
               + SUBSTRING(@hexstring, @firstint  + 1, 1)
               + SUBSTRING(@hexstring, @secondint + 1, 1)
           SELECT @i = @i + 1
       END
   END
   GO
   IF OBJECT_ID('dbo.sp_help_revlogin') IS NOT NULL
       DROP PROCEDURE dbo.sp_help_revlogin
   GO
   CREATE PROCEDURE dbo.sp_help_revlogin
       @login_name [sysname] = NULL
   AS
   BEGIN
       DECLARE @name                  [sysname]
       DECLARE @type                  [nvarchar](1)
       DECLARE @hasaccess             [int]
       DECLARE @denylogin             [int]
       DECLARE @is_disabled           [int]
       DECLARE @PWD_varbinary         [varbinary](256)
       DECLARE @PWD_string            [nvarchar](514)
       DECLARE @SID_varbinary         [varbinary](85)
       DECLARE @SID_string            [nvarchar](514)
       DECLARE @tmpstr                [nvarchar](4000)
       DECLARE @is_policy_checked     [nvarchar](3)
       DECLARE @is_expiration_checked [nvarchar](3)
       DECLARE @Prefix                [nvarchar](4000)
       DECLARE @defaultdb             [sysname]
       DECLARE @defaultlanguage       [sysname]
       DECLARE @tmpstrRole            [nvarchar](4000)
       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,default_language_name = ISNULL(p.default_language_name,@@LANGUAGE)
           FROM sys.server_principals p
           LEFT JOIN sys.syslogins l ON l.[name] = p.[name]
           WHERE p.[type] IN ('S' /* SQL_LOGIN */,'G' /* WINDOWS_GROUP */,'U' /* WINDOWS_LOGIN */)
               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,default_language_name = ISNULL(p.default_language_name,@@LANGUAGE)
           FROM sys.server_principals p
           LEFT JOIN sys.syslogins l ON l.[name] = p.[name]
           WHERE p.[type] IN ('S' /* SQL_LOGIN */,'G' /* WINDOWS_GROUP */,'U' /* WINDOWS_LOGIN */)
               AND p.[name] <> 'sa'
               AND p.[name] NOT LIKE '##%'
               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 for ' + QUOTENAME(@login_name) + N'. */'
           CLOSE login_curs
           DEALLOCATE login_curs
           RETURN - 1
       END
       SET @tmpstr = N'/* sp_help_revlogin script
   ** Generated ' + CONVERT([nvarchar], GETDATE()) + N' on ' + @@SERVERNAME + N'
   */'
       PRINT @tmpstr
       WHILE (@@fetch_status <> - 1)
       BEGIN
           IF (@@fetch_status <> - 2)
           BEGIN
               PRINT ''
               SET @tmpstr = N'/* Login ' + QUOTENAME(@name) + N' */'
               PRINT @tmpstr
               SET @tmpstr = N'IF NOT EXISTS (
       SELECT 1
       FROM sys.server_principals
       WHERE [name] = N''' + @name + N'''
       )
   BEGIN'
               PRINT @tmpstr
               IF @type IN ('G','U') -- NT-authenticated Group/User
               BEGIN -- NT authenticated account/group 
                   SET @tmpstr = N'    CREATE LOGIN ' + QUOTENAME(@name) + N'
       FROM WINDOWS
       WITH DEFAULT_DATABASE = ' + QUOTENAME(@defaultdb) + N'
           ,DEFAULT_LANGUAGE = ' + QUOTENAME(@defaultlanguage)
               END
               ELSE
               BEGIN -- SQL Server authentication
                   -- obtain password and sid
                   SET @PWD_varbinary = CAST(LOGINPROPERTY(@name, 'PasswordHash') AS [varbinary](256))
                   EXEC dbo.sp_hexadecimal @PWD_varbinary, @PWD_string OUT
                   EXEC dbo.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 = NCHAR(9) + N'CREATE LOGIN ' + QUOTENAME(@name) + N'
       WITH PASSWORD = ' + @PWD_string + N' HASHED
           ,SID = ' + @SID_string + N'
           ,DEFAULT_DATABASE = ' + QUOTENAME(@defaultdb) + N'
           ,DEFAULT_LANGUAGE = ' + QUOTENAME(@defaultlanguage)
   
                   IF @is_policy_checked IS NOT NULL
                   BEGIN
                       SET @tmpstr = @tmpstr + N'
           ,CHECK_POLICY = ' + @is_policy_checked
                   END
   
                   IF @is_expiration_checked IS NOT NULL
                   BEGIN
                       SET @tmpstr = @tmpstr + N'
           ,CHECK_EXPIRATION = ' + @is_expiration_checked
                   END
               END
               IF (@denylogin = 1)
               BEGIN -- login is denied access
                   SET @tmpstr = @tmpstr
                       + NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                       + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'DENY CONNECT SQL TO ' + QUOTENAME(@name)
               END
               ELSE IF (@hasaccess = 0)
               BEGIN -- login exists but does not have access
                   SET @tmpstr = @tmpstr
                       + NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                       + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'REVOKE CONNECT SQL TO ' + QUOTENAME(@name)
               END
               IF (@is_disabled = 1)
               BEGIN -- login is disabled
                   SET @tmpstr = @tmpstr
                       + NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                       + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'ALTER LOGIN ' + QUOTENAME(@name) + N' DISABLE'
               END
               SET @Prefix =
                   NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                   + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'EXEC [master].dbo.sp_addsrvrolemember @loginame = N'''
               SET @tmpstrRole = N''
               SELECT @tmpstrRole = @tmpstrRole
                   + CASE WHEN sysadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''sysadmin''' ELSE '' END
                   + CASE WHEN securityadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''securityadmin''' ELSE '' END
                   + CASE WHEN serveradmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''serveradmin''' ELSE '' END
                   + CASE WHEN setupadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''setupadmin''' ELSE '' END
                   + CASE WHEN processadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''processadmin''' ELSE '' END
                   + CASE WHEN diskadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''diskadmin''' ELSE '' END
                   + CASE WHEN dbcreator = 1 THEN @Prefix + LoginName + N''', @rolename = N''dbcreator''' ELSE '' END
                   + CASE WHEN bulkadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''bulkadmin''' ELSE '' END
               FROM (
                   SELECT
                       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
               IF @tmpstr <> '' PRINT @tmpstr
               IF @tmpstrRole <> '' 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
   

   Kommentar

   Det här skriptet skapar två lagrade procedurer i huvuddatabasen. Procedurerna heter sp_hexadecimal och sp_help_revlogin.

2. I SSMS-frågeredigeraren väljer du alternativet Resultat till text .

3. Kör följande instruktion i samma eller ett nytt frågefönster:

   EXEC sp_help_revlogin
   

4. Utdataskriptet som den sp_help_revlogin lagrade proceduren genererar är inloggningsskriptet. Det här inloggningsskriptet skapar de inloggningar som har den ursprungliga säkerhetsidentifieraren (SID) och det ursprungliga lösenordet.

5. Granska och följ informationen i avsnittet Ytterligare överväganden vid överföring av SQL Server-inloggningar innan du fortsätter med implementeringsstegen på målservern.

6. När du har slutfört alla tillämpliga steg i avsnittet Ytterligare överväganden vid överföring av SQL Server-inloggningar ansluter du till målserver B med hjälp av valfritt klientverktyg (till exempel SSMS).

7. Kör skriptet som genereras som utdata sp_helprevlogin från server A.

Ytterligare överväganden vid överföring av SQL Server-inloggningar

Granska följande information innan du kör utdataskriptet på instansen på server B:

Förstå lösenordshashing i SQL Server-inloggningsöverföringar

SQL Server hashar lösenord på följande sätt:

• VERSION_SHA1: Använder SHA1-algoritmen. SQL Server 2000 till SQL Server 2008 R2 använder den här hashen. De här versionerna har inte längre ordinarie eller utökat stöd, så du bör bara stöta på VERSION_SHA1 hashar när du migrerar bort från äldre instanser.
• VERSION_SHA2: Använder SHA2-512-algoritmen. SQL Server 2012 och senare, inklusive versioner som för närvarande stöds, använder denna hash.

Utdataskriptet skapar inloggningarna med hjälp av det krypterade lösenordet. Argumentet HASHED i CREATE LOGIN-instruktionen orsakar det här beteendet. Det här argumentet anger att lösenordet som angavs efter argumentet PASSWORD redan har hashats.

Hantera domänändringar under SQL Server-inloggningsöverföringar

Om käll- och målservrarna finns i olika domäner granskar du utdataskriptet noggrant. Ändra skriptet så att det ursprungliga domännamnet ersätts med det nya domännamnet i -uttrycken CREATE LOGIN . Integrerade inloggningar som beviljas åtkomst i den nya domänen delar inte samma SID som inloggningarna i den ursprungliga domänen, så användarna blir överblivna från dessa inloggningar. Information om hur du åtgärdar överblivna användare finns i Felsöka överblivna användare (SQL Server) och ALTER USER.

Om server A och server B finns i samma domän används samma SID. Därför är användarna inte överblivna.

Behörigheter som krävs för att visa och välja SQL Server-inloggningar

Som standard kan endast medlemmar i den fasta serverrollen sysadmin köra en SELECT instruktion mot sys.server_principals vyn. Om inte en sysadmin beviljar de behörigheter som krävs till andra användare kan dessa användare inte skapa eller köra utdataskriptet.

Standardinställningen för databasen är inte skriptad och överförd

Stegen i den här artikeln överför inte standarddatabasinformationen för en viss inloggning. Den här begränsningen finns eftersom standarddatabasen kanske inte alltid finns på server B. Om du vill definiera standarddatabasen för en inloggning använder du ALTER LOGIN-instruktionen genom att skicka in inloggningsnamnet och standarddatabasen som argument.

Hantera sorteringsordningsskillnader i SQL Server-inloggningsöverföringar

Käll- och målservrarna kan ha olika sorteringsordningar eller använda samma sorteringsordning. Så här kan du hantera varje scenario:

• Skiftlägesokänslig server A och skiftlägeskänslig server B: Sorteringsordningen för server A är skiftlägesokänslig och sorteringsordningen för server B är skiftlägeskänslig. I det här fallet måste användarna ange lösenorden i versaler när du har överfört inloggningarna och lösenorden till instansen på server B.

• Skiftlägeskänslig server A och skiftlägesokänslig server B: Sorteringsordningen för server A är skiftlägeskänslig och sorteringsordningen för server B är skiftlägesokänslig. I det här fallet kan användarna inte logga in med hjälp av de inloggningar och lösenord som du överför till instansen på server B såvida inte något av följande villkor är sant:

  • De ursprungliga lösenorden innehåller inga bokstäver.
  • Alla bokstäver i de ursprungliga lösenorden är versaler.

• Skiftlägeskänslig eller ej skiftlägeskänslig på båda servrarna: Sorteringsordningen för både server A och server B är skiftlägeskänslig, eller så är sorteringsordningen för både server A och server B ej skiftlägeskänslig. I dessa fall uppstår inga problem för användarna.

Åtgärda konflikter med befintliga inloggningar på målservern

Skriptet kontrollerar om inloggningen finns på målservern och skapar endast en inloggning om den inte finns. Men om du får följande felmeddelande när du kör utdataskriptet på instansen på server B måste du lösa konflikten manuellt genom att följa stegen i det här avsnittet.

Msg 15025, nivå 16, status 1, rad 1
Serverhuvudnamnet "MyLogin" finns redan.

På samma sätt kan en inloggning som redan finns i instansen på server B ha ett SID som är detsamma som ett SID i utdataskriptet. I det här fallet får du följande felmeddelande när du kör utdataskriptet på instansen på server B:

Msg 15433, nivå 16, status 1, rad 1 Parametern sid som angavs används redan.

Följ dessa steg för att åtgärda konflikten manuellt:

1. Granska utdataskriptet noggrant.
2. Granska innehållet i sys.server_principalsvyn i instansen på server B.
3. Vidta lämpliga åtgärder för varje fel, till exempel att släppa eller byta namn på den motstridiga inloggningen på server B eller ta bort duplicerade CREATE LOGIN instruktioner från utdataskriptet innan du kör det igen.

I SQL Server styr SID för en inloggning åtkomst på databasnivå. En inloggning kan ha olika SID:er när den mappas till användare i olika databaser, vilket kan inträffa om du manuellt kombinerar databaser från olika servrar. I så fall kan inloggningen endast använda den databas där databashuvudmannens SID matchar det SID som finns i vyn sys.server_principals. Lös problemet genom att släppa databasanvändaren som har det felmatchade SID:et med hjälp av DROP USER-instruktionen . Lägg sedan till användaren igen med -instruktionen CREATE USER och mappa den till rätt inloggning (serverns huvudnamn).

Mer information om server- och databasobjekt finns i SKAPA ANVÄNDARE och SKAPA INLOGGNING.

Avancerade scenarier och felsökning

Om du fortsätter att se inloggningsfel när du har överfört inloggningar kontrollerar du följande:

• Överblivna databasanvändare: Kör sys.sp_change_users_login (äldre) eller använd ALTER USER ... WITH LOGIN = ... för att mappa om databasanvändare till de överförda inloggningarna. Mer information finns i Felsöka överblivna användare (SQL Server).
• Inneslutna databaser: Själva databasen lagrar inloggningar för användare i en innesluten databas, så att de flyttas med den. Du behöver inte överföra de här användarna med hjälp av sp_help_revlogin.
• AlwaysOn-tillgänglighetsgrupper och redundansklusterinstanser: Överför inloggningar till varje replik eller nod så att användarna kan logga in efter en redundansväxling. Mer information finns i Hantera inloggningar för jobb med hjälp av databaser i en AlwaysOn-tillgänglighetsgrupp.
• Azure SQL Managed Instance och Azure SQL Database: Inloggningsöverföring fungerar annorlunda i Azure. Se Migrera inloggningar mellan SQL Server och SQL Managed Instance och Hantera inloggningar och användare i Azure SQL Database.
• Inloggningsfel 18456: Andra orsaker och korrigeringar finns i MSSQLSERVER_18456.

Relaterat innehåll

• Felsöka överblivna användare (SQL Server)
• SKAPA INLOGGNING (Transact-SQL)
• ÄNDRA INLOGGNING (Transact-SQL)
• Skriptobjekt i SQL Server Management Studio