SQL Server 2005 のインスタンス間でログインおよびパスワードを転送する方法

中小企業のお客様は、中小企業向けサポートサイトで問題解決コンテンツや学習リソースもご利用ください。
はじめに
この資料では、異なるサーバー上にある Microsoft SQL Server 2005 のインスタンス間でログインおよびパスワードを転送する方法について説明します。

他のバージョンの SQL Server のインスタンスとの間でログインおよびパスワードを転送する方法の詳細を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
246133 SQL Server のインスタンス間でログインおよびパスワードを転送する方法
詳細
この資料では、サーバー A とサーバー B が異なるサーバーであり、両方のサーバーで SQL Server 2005 が実行されている場合について説明します。

サーバー A の SQL Server のインスタンスからサーバー B の SQL Server のインスタンスにデータベースを移動した後、サーバー B のデータベースにログインできない場合があります。また、次のエラー メッセージが表示されることがあります。
ユーザー 'MyUser' はログインできませんでした。(Microsoft SQL Server、エラー: 18456)
この問題は、サーバー A の SQL Server のインスタンスからサーバー B の SQL Server のインスタンスにログインおよびパスワードを転送していないことが原因で発生します。

サーバー A の SQL Server のインスタンスからサーバー B の SQL Server のインスタンスにログインおよびパスワードを転送するには、次の手順を実行します。
  1. サーバー A で SQL Server Management Studio を起動し、データベースの移動元である SQL Server のインスタンスに接続します。
  2. 新しいクエリ エディタ ウィンドウを開き、次のスクリプトを実行します。
    USE masterGOIF OBJECT_ID ('sp_hexadecimal') IS NOT NULL  DROP PROCEDURE sp_hexadecimalGOCREATE PROCEDURE sp_hexadecimal    @binvalue varbinary(256),    @hexvalue varchar (514) OUTPUTASDECLARE @charvalue varchar (514)DECLARE @i intDECLARE @length intDECLARE @hexstring char(16)SELECT @charvalue = '0x'SELECT @i = 1SELECT @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 + 1ENDSELECT @hexvalue = @charvalueGO IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL  DROP PROCEDURE sp_help_revloginGOCREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL ASDECLARE @name sysnameDECLARE @type varchar (1)DECLARE @hasaccess intDECLARE @denylogin intDECLARE @is_disabled intDECLARE @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_nameOPEN login_cursFETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denyloginIF (@@fetch_status = -1)BEGIN  PRINT 'No login(s) found.'  CLOSE login_curs  DEALLOCATE login_curs  RETURN -1ENDSET @tmpstr = '/* sp_help_revlogin script 'PRINT @tmpstrSET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'PRINT @tmpstrPRINT ''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   ENDCLOSE login_cursDEALLOCATE login_cursRETURN 0GO
    : このスクリプトにより、それぞれ sp_hexadecimal および sp_help_revlogin という名前の 2 つのストアド プロシージャが master データベースに作成されます。
  3. 次のステートメントを実行します。
    EXEC sp_help_revlogin
    sp_help_revlogin ストアド プロシージャにより生成される出力スクリプトはログイン スクリプトです。このログイン スクリプトにより、元のセキュリティ識別子 (SID) と元のパスワードを持つログインが作成されます。
  4. サーバー B で SQL Server Management Studio を起動し、データベースの移動先である SQL Server のインスタンスに接続します。

    重要 : 手順 5. に進む前に、「解説」に記載されている情報を参照してください。
  5. 新しいクエリ エディタ ウィンドウを開き、手順 3. で生成された出力スクリプトを実行します。

解説

サーバー B のインスタンスで出力スクリプトを実行する前に、以下の情報を参照してください。
  • 出力スクリプトを注意して調べます。サーバー A とサーバー B が異なるドメインにある場合は、出力スクリプトを変更する必要があります。この場合は、CREATE LOGIN ステートメントの元のドメイン名を新しいドメイン名に置き換える必要があります。新しいドメイン内でアクセスが許可されている統合ログインには、元のドメインのログインと同じ SID がありません。このため、ユーザーはこれらのログインでは不明なユーザーとなります。これらの不明なユーザーを解決する方法の詳細を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
    240872 [HOWTO] SQL Server を実行しているサーバー間でデータベースを移動するときに、権限の問題を解決する方法
    サーバー A とサーバー B が同じドメインにある場合は、同じ SID が使用されます。したがって、通常は、ユーザーが不明なユーザーになることはありません。
  • 出力スクリプトで、ログインは暗号化されたパスワードを使用して作成されます。これは、CREATE LOGIN ステートメントに HASHED 引数が指定されているためです。この引数は、PASSWORD 引数の後に入力したパスワードが既にハッシュされていることを示します。
  • デフォルトでは、sysadmin 固定サーバー ロールのメンバだけが sys.server_principals ビューで SELECT ステートメントを実行できます。sysadmin 固定サーバー ロールのメンバでユーザーに対して必要なアクセス許可を与えていない限り、ユーザーは出力スクリプトを作成したり実行したりすることができません。
  • この資料の手順では、特定のログインに対する既定のデータベースの情報を転送していません。これは、既定のデータベースが必ずしもサーバー B に存在するとは限らないためです。ログインに対する既定のデータベースを定義するには、ALTER LOGIN ステートメントを使用し、引数としてログイン名と既定のデータベースを渡します。
  • サーバー A の並べ替え順では大文字と小文字が区別されておらず、サーバー B の並べ替え順では大文字と小文字が区別されている場合があります。この場合は、ログインおよびパスワードがサーバー B のインスタンスに転送された後、ユーザーはパスワードのすべての文字を大文字で入力する必要があります。

    逆に、サーバー A の並べ替え順では大文字と小文字が区別されており、サーバー B の並べ替え順では大文字と小文字が区別されていない場合があります。この場合は、次のいずれかの条件に該当していない限り、サーバー B のインスタンスに転送したログインおよびパスワードを使用してユーザーがログインすることはできません。
    • 元のパスワードに文字が 1 つも含まれていない。
    • 元のパスワードの文字がすべて大文字である。
    サーバー A とサーバー B のいずれでも並べ替え順で大文字と小文字が区別されている場合や、サーバー A とサーバー B のいずれでも並べ替え順で大文字と小文字が区別されていない場合があります。これらの場合は、ユーザーのログインで問題は発生しません。
  • サーバー B のインスタンスに既に存在するログインの名前が出力スクリプトにある名前と同じである場合があります。この場合は、サーバー B のインスタンスで出力スクリプトを実行したときに次のエラー メッセージが表示されます。
    メッセージ 15025、レベル 16、状態 1、行 1
    サーバー プリンシパル 'MyLogin' は既に存在します。
    同様に、サーバー B のインスタンスに既に存在するログインの SID が出力スクリプトにある SID と同じである場合があります。この場合は、サーバー B のインスタンスで出力スクリプトを実行したときに次のエラー メッセージが表示されます。
    メッセージ 15433、レベル 16、状態 1、行 1
    指定されたパラメータ sid は使用中です。
    この場合は、次の作業を実行する必要があります。
    1. 出力スクリプトを注意して調べます。
    2. サーバー B のインスタンスで sys.server_principals ビューの内容を確認します。
    3. それぞれのエラー メッセージに対処します。
  • SQL Server 2005 では、ログインの SID はデータベース レベルのアクセスを実装するための基準として使用されます。あるログインに対して、1 つのサーバー上にある 2 つの異なるデータベースで、それぞれ別の SID が割り当てられている場合があります。この場合、このログインでは、sys.server_principals ビューの SID と一致する SID を持つデータベースにのみアクセスできます。この問題は、それぞれ別のサーバーから 2 つのデータベースが統合された場合に発生することがあります。この問題を解決するには、DROP USER ステートメントを使用して、一致していない SID を持つデータベースからログインを手動で削除します。次に CREATE USER ステートメントを使用して、ログインを再度追加します。
関連情報
不明なユーザーのトラブルシューティング方法の詳細については、以下の MSDN (Microsoft Developer Network) Web サイトを参照してください。CREATE LOGIN ステートメントの詳細については、以下の MSDN Web サイトを参照してください。ALTER LOGIN ステートメントの詳細を参照については、以下の MSDN Web サイトを参照してください。
プロパティ

文書番号:918992 - 最終更新日: 07/16/2013 01:52:00 - リビジョン: 4.1

Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Workgroup Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition

  • kbsqlmanagementtools kbexpertiseadvanced kbhowto kbinfo KB918992
フィードバック