Kobarin's Development Blog

C#やASP.NET、公開APIなどについての記録です。

データベースユーザーを、SQL Serverログインにマッピング

SqlServerログインとデータベースユーザーのマッピングについて解説しています。

用語

  • データベースユーザー…SSMSの[DB]-[セキュリティ]-[ユーザ]内にあるアカウント
  • SQL serverログイン…SSMSの[セキュリティ]-[ログイン]内にあるアカウント

なぜマッピングが必要か?

例えば開発時において、本番サーバーのDBのバックアップファイルを開発用マシンに復元した場合、本番サーバー時のデータベースユーザー「userA」で接続しようとしても出来ません。
理由は「userA」が開発用SQLのログインに登録されていないために起こる現象です。

一般的な誤り

通常まず最初に思い浮かぶのは、「SQLserverログインを追加すればよいのでは?」だと思います。私も同じように考えました。
ではまず、開発用SQLserverにログイン「userA」を追加してSQLserverに接続を試みます。
しかしこれだけでは接続できません。
理由は、開発用SQLserverに登録されているデータベースユーザー「userA」とログイン「userA」がマップされていない(関連付けがされていない)ためです。
名前は同じ「userA」でも、内部的に関連付けるための「sid」が異なるのです。双方のsidが一致してこそ初めて「ログイン'userA'は、データベースユーザー'userA'と同じ」と見なされるのです。

マッピングの方法

したがって、sidを共通化させる事ができれば、データベースユーザー「userA」がログイン「userA」にマップされる事になります。
この操作は以下のとおりです。

use <database name>;
EXEC sp_change_users_login 'Update_One','<database user name>','<login name>'
GO
Update_One
固定文句です。公式サイトでは「現在のデータベースに存在する、指定した user を、既存の SQL Server login にリンクします。user および login を指定する必要があります。password は NULL にするか、指定しないでください。」
<database user name>
データベースユーザー名
<login name>
ログイン名。予め手動で登録する必要あり

補足

上述している「sp_change_users_login」は将来的に廃止されるため「ALTER USER」が推奨されるようです。
SQL Server 2008 データベースの復元後にデータベース上のユーザーログインと権限が無効になった。