SQL Server のインスタンス間でログインとパスワードを転送する

[アーティクル]
09/06/2024

この記事では、Windows で実行されている SQL Server の異なるインスタンス間で、ログインとパスワードを転送する方法について説明します。

元の製品バージョン: SQL Server
元の KB 番号： 918992、246133

はじめに

この記事では、Microsoft SQL Server の異なるインスタンス間で、ログインとパスワードを転送する方法について説明します。

注:

インスタンスは、同じサーバー上または異なるサーバー上にあり、それらのバージョンが異なる場合があります。

詳細情報

この記事では、サーバー A とサーバー B は別のサーバーです。

サーバー A 上のSQL Serverのインスタンスからサーバー B 上のSQL Serverのインスタンスにデータベースを移動した後、ユーザーはサーバー B 上のデータベースにログインできない可能性があります。さらに、ユーザーは次のエラーメッセージを受け取ることがあります。

ユーザー 'MyUser' はログインできませんでした。 (Microsoft SQL Server、エラー: 18456)

この問題は、ログインとパスワードをサーバー A 上の SQL Server のインスタンスからサーバー B 上のSQL Serverのインスタンスに転送しなかったために発生します。

注:

18456 のエラーメッセージは、他の理由によっても発生します。これらの原因と考えられる解決策の詳細については、「 MSSQLSERVER_18456」を参照してください。

ログインを転送するには、状況に応じて、次のいずれかの方法を使用します。

方法 1: コピー先のコンピューター (サーバー B) SQL Serverパスワードをリセットします。

この問題を解決するには、SQL Server コンピューターでパスワードをリセットし、ログインをスクリプト化します。

注:

パスワードをリセットする際、パスワードハッシュアルゴリズムが使用されます。

方法 2: ソースサーバー (サーバー A) で生成されたスクリプトを使用して、ログインとパスワードを移行先サーバー (サーバー B) に転送します。

ログインとそのパスワードの転送に必要なスクリプトを生成するために、ストアドプロシージャを作成します。これを行うには、SQL Server Management Studio (SSMS) またはその他のクライアントツールを使用してサーバー A に接続し、次のスクリプトを実行します。

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

注:

このスクリプトは、マスターデータベースに 2 つのストアドプロシージャを作成します。プロシージャには sp_hexadecimal と sp_help_revlogin という名前が付 けられます。

SSMS クエリエディターで、[結果からテキストへ] オプションを選択します。
同じクエリウィンドウまたは新しいクエリウィンドウで次のステートメントを実行します。
```
EXEC sp_help_revlogin
```
sp_help_revlogin ストアドプロシージャで生成される出力スクリプトは、ログインスクリプトです。このログインスクリプトは、元のセキュリティ識別子 (SID) と元のパスワードでのログインを行います。

重要

移行先サーバーでの手順の実装に進む前に、次の備考セクションの情報を確認してください。

転送先サーバー (サーバー B) での手順

任意のクライアントツール (SSMS など) を使用してサーバー B に接続し、手順 4 で生成されたスクリプト (の sp_helprevlogin出力) をサーバー A から実行します。

注釈

サーバー B 上のインスタンスで出力スクリプトを実行する前に、次の情報を確認します。

パスワードは、次の方法でハッシュされている可能性があります。
- VERSION_SHA1: このハッシュは SHA1 アルゴリズムを使用して生成され、SQL Server 2000 から SQL Server 2008 R2 で使用されます。
- VERSION_SHA2: このハッシュは SHA2 512 アルゴリズムを使用して生成され、SQL Server 2012 以降のバージョンで使用されます。
出力スクリプトを注意深く確認してください。サーバー A とサーバー B が異なるドメインにある場合は、出力スクリプトを変更する必要があります。次に、ステートメントで新しいドメイン名を使用して、元のドメイン名を CREATE LOGIN 置き換える必要があります。新しいドメインでアクセス権が付与された統合ログインには、元のドメインのログインと同じ SID がありません。そのため、ユーザーはこれらのログインを行えずに孤立します。孤立したユーザーを解決する方法の詳細については、「孤立したユーザー (SQL Server) と ALTER USER のトラブルシューティング」を参照してください。
サーバー A とサーバー B が同じドメイン内にある場合は、同じ SID が使用されます。そのため、ユーザーが孤立する可能性は低いです。
出力スクリプトでは、暗号化されたパスワードを使用してログインが作成されます。これは、CREATE LOGIN ステートメント内の HASHED 引数に基づいています。この引数は、PASSWORD 引数の後に入力されたパスワードが既にハッシュされていることを指定します。
既定では、sysadmin 固定サーバーロールのメンバーのみが sys.server_principals ビューから SELECT ステートメントを実行できます。 sysadmin 固定サーバーロールのメンバーがユーザーに必要なアクセス許可を付与しない限り、ユーザーは出力スクリプトを作成または実行できません。
この記事の手順では、特定のログインの既定のデータベース情報は転送されません。これは、既定のデータベースがサーバー B に常に存在するとは限らないためです。ログインの既定のデータベースを定義するには、ステートメントを ALTER LOGIN 使用して、ログイン名と既定のデータベースを引数として渡します。
ソースサーバーと宛先サーバーの並べ替え順序:
- 大文字と小文字を区別しないサーバー A と大文字と小文字を区別するサーバー B: サーバー A の並べ替え順序では大文字と小文字が区別されず、サーバー B の並べ替え順序では大文字と小文字が区別される場合があります。この場合、ユーザーは、ログインとパスワードをサーバー B のインスタンスに転送した後、すべて大文字でパスワードを入力する必要があります。
- 大文字と小文字を区別するサーバー A と大文字と小文字を区別しないサーバー B: サーバー A の並べ替え順序は大文字と小文字が区別され、サーバー B の並べ替え順序では大文字と小文字が区別される場合があります。この場合、次のいずれかの条件が当てはまる場合を除き、ユーザーはログインとサーバー B 上のインスタンスに転送するパスワードを使用してログインできません。
  - 元のパスワードには文字が含まれていない。
  - 元のパスワードのすべての文字は大文字です。
- 両方のサーバーで大文字と小文字を区別するか、大文字と小文字を区別しない: サーバー A とサーバー B の両方の並べ替え順序で大文字と小文字が区別される場合や、サーバー A とサーバー B の両方の並べ替え順序で大文字と小文字が区別される場合があります。このような場合、ユーザーに問題は発生しません。
サーバー B 上のインスタンスに既に存在するログインには、出力スクリプトの名前と同じ名前が含まれている場合があります。この場合、サーバー B 上のインスタンスで出力スクリプトを実行すると、次のエラーメッセージが表示されます。

メッセージ 15025、レベル 16、状態 1、行 1
サーバープリンシパル 'MyLogin' は既に存在します。

同様に、サーバー B 上のインスタンスに既に存在するログインには、出力スクリプトの SID と同じ SID がある場合があります。この場合、サーバー B 上のインスタンスで出力スクリプトを実行すると、次のエラーメッセージが表示されます。

Msg 15433、レベル 16、状態 1、行 1 指定されたパラメータ sid は使用中です。

この場合は、次の作業を実行する必要があります。
1. 出力スクリプトを注意深く確認してください。
2. サーバー B 上の sys.server_principals インスタンスのビューの内容を調べます。
3. 必要に応じて、これらのエラーメッセージに対処します。
  
  SQL Server 2005 では、ログインの SID を使用してデータベースレベルのアクセスを実装します。ログインは、サーバー上の異なるデータベースで異なる SID を持つ場合があります。この場合、ログインはビュー内 sys.server_principals の SID と一致する SID を持つデータベースにのみアクセスできます。この問題は、2 つのデータベースが異なるサーバーから結合されている場合に発生する可能性があります。この問題を解決するには、DROP USER ステートメントを使用して SID が一致しないデータベースからのログインを手動で削除します。次に、ステートメントを使用して CREATE USER ログインをもう一度追加します。

次の方法で共有

SQL Server のインスタンス間でログインとパスワードを転送する

はじめに

詳細情報

転送先サーバー (サーバー B) での手順

注釈

関連情報

フィードバック

その他のリソース