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

Artikel
01/17/2025

I den här artikeln beskrivs hur du överför inloggningar och lösenord mellan olika instanser av SQL Server som körs i Windows.

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

Introduktion

I den här artikeln beskrivs hur du överför inloggningar och lösenord mellan olika instanser av Microsoft SQL Server. Instanserna 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?

I den här artikeln är server A och server B servrar.

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.

Tänk på att fel 18456 inträffar av många andra orsaker. Mer information om dessa orsaker och deras lösningar finns i MSSQLSERVER_18456.

Steg för att överföra inloggningarna

För att överföra inloggningarna använder du någon av följande metoder, beroende på din situation.

Metod 1: Generera skript via SSMS på källservern och återställa lösenord manuellt för SQL Server-inloggningar på målservern

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:

Anslut till server A som är värd för SQL Server-källan.
Expandera noden Databaser.
Högerklicka på valfri användardatabas och välj Uppgifter>Generera skript.
Sidan Introduktion öppnas. Välj Nästa för att öppna sidan Välj objekt . Välj Skripta hela databasen och alla databasobjekt.
Välj Nästa för att öppna sidan Ange skriptalternativ .
Välj knappen Avancerat för alternativ för skriptinloggning.
I listan Avancerat letar du reda på Skriptinloggningar, anger alternativet till Sant och väljer OK.
Tillbaka till Ange skriptalternativ under Välj hur skript ska sparas och välj Öppna i nytt frågefönster.
Välj Nästa två gånger och välj sedan Slutför.
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

Detta anger att SQL Server-autentiseringsinloggningarna 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.
Använd inloggningsskriptet från det större genererade skriptet på sql-målservern.
För alla SQL Server-autentiseringsinloggningar återställer du lösenordet på SQL Server-målet och återaktiverar dessa inloggningar.

Metod 2: Överföra inloggningar och lösenord till målservern (Server B) med skript som genereras på källservern (Server A)

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.

I SSMS-frågeredigeraren väljer du alternativet Resultat till text.
Kör följande sats i samma eller ett nytt frågefönster:
```
EXEC sp_help_revlogin
```
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.
Granska och följ informationen i avsnittet Anmärkningar innan du fortsätter med implementeringsstegen på målservern.
När du har implementerat alla tillämpliga steg från avsnittet Anmärkningar ansluter du till målserverN B med alla klientverktyg (till exempel SSMS).
Kör skriptet som genereras som utdata sp_helprevlogin från server A.

Kommentarer

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

Information om lösenordshashing

Ett lösenord kan hashas på följande sätt:
- VERSION_SHA1: Denna hash genereras med hjälp av SHA1-algoritmen och används i SQL Server 2000 till och med SQL Server 2008 R2.
- VERSION_SHA2: Denna hash genereras med hjälp av SHA2 512-algoritmen och används i SQL Server 2012 och senare versioner.
I utdataskriptet skapas inloggningarna med hjälp av det krypterade lösenordet. Detta beror på HASHED argumentet i -instruktionen CREATE LOGIN . Det här argumentet anger att lösenordet som anges efter att PASSWORD argumentet redan har hashats.

Hantera ändringar av domäner

Finns dina käll- och målservrar i olika domäner? Granska utdataskriptet noggrant. Om server A och server B finns i olika domäner måste du ändra utdataskriptet. Sedan måste du ersätta det ursprungliga domännamnet med hjälp av det nya domännamnet i -uttrycken CREATE LOGIN . De integrerade inloggningar som beviljas åtkomst i den nya domänen har inte samma SID som inloggningarna i den ursprungliga domänen. Därför blir användarna överblivna från dessa inloggningar. Mer information om hur du löser dessa ö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 det osannolikt att användarna blir överblivna.

Behörigheter för att visa och välja alla inloggningar

Som standard kan endast en medlem i den fasta sysadmin-serverrollen köra en SELECT-instruktion från vyn sys.server_principals. Om inte en medlem i den fasta sysadmin-serverrollen ger användarna nödvändiga behörigheter kan användarna 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. Det beror på att standarddatabasen kanske inte alltid finns på server B. Om du vill definiera standarddatabasen för en inloggning använder du -instruktionen ALTER LOGIN genom att skicka in inloggningsnamnet och standarddatabasen som argument.

Hantera olika sorteringsbeställningar mellan käll- och målservrarna

Det kan finnas skillnader i sorteringsordningar mellan käll- och målservrarna, eller så kan de vara desamma. Så här kan du åtgärda varje scenario:

Skiftlägesokänslig server A och skiftlägeskänslig server B: Sorteringsordningen för server A kan vara skiftlägeskänslig och sorteringsordningen för server B kan vara 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 kan vara skiftlägeskänslig och sorteringsordningen för server B kan vara skiftlägeskä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 skiftlägeskänslig på båda servrarna: Sorteringsordningen för både server A och server B kan vara skiftlägeskänslig, eller så kan sorteringsordningen för både server A och server B vara skiftlägeskänslig. I dessa fall uppstår inga problem för användarna.

Hantera inloggningar som redan finns på målservern

Skriptet är utformat för att kontrollera om inloggningen finns på målservern och skapa en inloggning endast om den inte gör det. Men om du får följande felmeddelande när du kör utdataskriptet på instansen på server B måste du lösa det 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 – medföljande sid-parameter används.

Lös problemet manuellt genom att följa dessa steg:

Granska utdataskriptet noggrant.
Granska innehållet sys.server_principals i vyn i instansen på server B.
Åtgärda dessa felmeddelanden efter behov.

Från och med SQL Server 2005 används SID för en inloggning för att hantera åtkomst på databasnivå. Ibland kan en inloggning ha olika SID:er när de mappas till användare i olika databaser. Det här problemet kan inträffa om databaser kombineras manuellt från olika servrar. I sådana fall kan inloggningen bara komma åt databasen där databasobjektets SID matchar SID i sys.server_principals vyn. Lös problemet genom att manuellt ta bort databasanvändaren med det felmatchade SID 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 och för att skilja servrar från databashuvudnamn finns i SKAPA ANVÄNDARE och SKAPA INLOGGNING.

Dela via