SQL Server 2005 Tips and Tips

ログイン アカウントのバックアップと復元は、意外とトラブルに陥りやすいところです。特に、異なる SQL Server 間でのログイン アカウントの移動 (転送) は、標準で提供されるツールではパスワードと SID (Security ID) をそのまま転送することができないため、注意が必要です。同一のパスワード/SID のログイン アカウントがきちんと転送されていない場合には、データベース ユーザーは利用できなくなり、そのユーザーに設定されたオブジェクト権限も機能しなくなってしまいます。
このことは、データベース ミラーリングやログ配布機能にも当てはまります。データベース ミラーリングやログ配布は、あくまでも特定のデータベースを複製する機能なので、ログイン アカウントのようにシステム データベースへ格納される情報は、別途複製しなければならないのです。

トピック

	異なるサーバー間でのログイン アカウントの転送　〜不明なデータベース ユーザーの発生〜
	ログイン アカウントを転送するには？　SQL Server 認証用のログイン アカウントのパスワードと SID に注意！
	同じパスワード/SID のログイン アカウントの作成　sp_help_revlogin

異なるサーバー間でのログイン アカウントの転送　〜不明なデータベース ユーザーの発生〜

開発、テスト環境から本番環境へ、あるいはその逆へ、異なるサーバー間でデータベースを移行するケースは非常に多いと思います。このとき、単純なデータ移動であれば、BACKUP ステートメントでバックアップしたファイルを RESTORE ステートメントで復元したり、データ ファイル (.mdf) とログ ファイル (.ldf) を直接コピーして、新しい環境へアタッチ (sp_attach_db) することで完了しますが、ログイン アカウントの移動となると、それだけでは完了しません。というのは、ログイン アカウントは master データベース内へ格納されているからです。
もし、データベースの移行先に同一のログイン アカウントが存在しない場合には、データベース ユーザーは利用できなくなり、そのユーザーに設定されたオブジェクト権限も機能しなくなります。これは、次のような状況です。

マッピングの切れた“不明なデータベース ユーザー”
拡大図を見る

この画面は、“testlogin 1”という名前のデータベース ユーザーのプロパティを表示しているところです。[ログイン名] セクションが“空”になっていることに注目してください。これは、データベース ユーザーに対応したログイン アカウントが存在しない場合に発生し、このようなデータベース ユーザーは“不明なデータベース ユーザー”と呼ばれます。この状態では、testlogin 1 ユーザーはデータベースへアクセスすることができません。つまり、異なるサーバー間でデータベースをバックアップから復元したり、アタッチしたりするだけでは、同一のログイン アカウントが存在しないことになり、このような状況が発生することになります。
なお、以下のように sp_change_users_login というシステム ストアド プロシージャを利用すれば、不明なデータベース ユーザーをリストアップすることもできます。

USE データベース名  
EXEC sp_change_users_login 'Report'  

このような不明なデータベース ユーザーを正しく利用できるようにするには、移行元と同一のログイン アカウントを作成しなければなりません。

ページのトップへ

ログイン アカウントを転送するには？　SQL Server 認証用のログイン アカウントのパスワードと SID に注意！

ログイン アカウントを転送するツールには、「データベース コピー ウィザード」と Integration Services の「ログイン転送タスク」、「SQL Server オブジェクトの転送タスク」などがあります。ログイン アカウントが Windows 認証用で、かつ Active Directory ドメイン アカウントの場合には、これらのツールを使ってログイン アカウントを転送することにより、不明なデータベース ユーザーが解消され、データベース ユーザーを移行元と同じように利用できるようになります。
しかし、ログイン アカウントが Windows のローカル ユーザーの場合には、注意が必要です。この場合には、移行元と移行先でそれぞれ異なる Windows ユーザーとなるため、ログイン アカウントの転送に失敗してしまうのです。しかも、これを回避する方法は、ログイン アカウントとデータベース ユーザーを移行先において手動で作り直すしかありません。
また、ログイン アカウントが SQL Server 認証用の場合にも注意が必要です。これらの転送ツールでは、ログイン アカウントのパスワードと SID (ログイン アカウントへ内部的に割り当てられた Security ID) を転送することができないからです (パスワードと SID は、転送先で新しく再作成されます)。データベース ユーザーは、ログイン アカウントの名前とではなく、SID とマッピングされるため、SID が異なる場合には、不明なデータベース ユーザーは解消されません。これを解決するには、以下のように sp_change_users_login を実行します。

USE データベース名  
EXEC sp_change_users_login 'Update_One',
'不明なデータベース ユーザー名','新しいログイン アカウント名' 

このように Update_One を指定して sp_change_users_login を実行すると、不明なデータベース ユーザーの古い SID (移行元の SID) を新しいログイン アカウントの SID (移行先の SID) へ更新し、再マッピングをしてくれるようになります。これで不明なデータベース ユーザーが解消され、移行元と同様にデータベース ユーザーが利用できるようになります。

ページのトップへ

同じパスワード/SID のログイン アカウントの作成　sp_help_revlogin

sp_change_users_login によるログイン アカウントの再マッピングは、データベース ユーザーの数が多い場合には、大変な作業です。そこで、同じパスワード/SID のログイン アカウントを一括で作成する方法を紹介します。これは、sp_help_revlogin という名前のストアド プロシージャで、マイクロソフトのサポート技術情報 (KB: Knowledge Base) の文書番号 918992 で提供されています。

KB918992: How to transfer the logins and the passwords between instances of SQL Server 2005
http://support.microsoft.com/default.aspx?scid=kb;en-us;918992

この文書で提供されるスクリプトを丸ごとコピーし、SQL Server 2005 Management Studio へ貼り付けて実行することによって、sp_help_revlogin ストアド プロシージャが作成されます。作成後、以下のように実行すれば、同一のパスワード/SID のログイン アカウントを作成するためのスクリプト (CREATE LOGIN ステートメント) が生成されます。

EXEC master..sp_help_revlogin 

sp_help_revlogin の実行結果
拡大図を見る

後は、生成された CREATE LOGIN ステートメントを移行先で実行すれば、同一のパスワード/SID のログイン アカウントを作成できようになり、不明なデータベース ユーザーが解消されます。このように sp_help_revlogin は、ログイン アカウントのバックアップと復元に利用できる大変便利なストアド プロシージャなので、ぜひご活用いただければと思います。
なお、sp_help_revlogin は、SQL Server 7.0/2000 のときは、サポート技術情報 (KB) の文書番号 246133 で提供されていました。

KB246133: How to transfer logins and passwords between instances of SQL Server
http://support.microsoft.com/?scid=kb;en-us;246133

しかし、このスクリプトは、あくまでも SQL Server 7.0/2000 用で、SQL Server 2005 に対しては実行できませんので注意してください。また、この KB246133 は、日本語版も提供されていますが、日本語版の文書は Revision が古いため、同一の SID を生成することができないことにご注意ください (SQL Server 70/2000 を利用している場合は、日本語版のスクリプトではなく、上記 URL の英語版のスクリプトを実行するようにしてください)。

ページのトップへ

3 of 9