はじめに
本番環境のDBをコピーして、開発環境をサクッと構築！」
SQL Serverを使っていると、そんな場面はよくありますよね。しかし、データベースを別のサーバーに**リストア（復元）**した直後、アプリケーションからDBに接続しようとすると、エラーが出てログインできない…。
そんな経験はありませんか？
これは、データベース内のユーザー（DBユーザー）と、サーバーへの接続許可を持つユーザー（ログイン）の紐付けが切れてしまうことで発生する、非常によくある問題です。この記事では、なぜこの問題が起きるのか、そしてたった2つのコマンドでこの不整合をスマートに解消する方法を、備忘録も兼ねて分かりやすく解説します。
なぜ問題が起きるの？「ユーザー」と「ログイン」のズレ
この問題を理解するために、SQL Serverのユーザー管理を「マンションのセキュリティ」に例えてみましょう。
- ログイン (SQL Server)
- マンションの**「顔認証システム」**に登録されている情報です。これで、そもそもマンションの建物に入れる人が決まります。
- DBユーザー (データベース内)
- 各部屋の**「鍵」**です。建物に入れた後、どの部屋に入れるかを決めます。
普段は、「Aさんの顔認証データ」と「Aさんの部屋の鍵」は正しく紐付いています。
しかし、データベースを別のサーバーにリストアする（＝別のマンションに引っ越す）と、部屋（データベース）とその中の「鍵」はそのままコピーされますが、引っ越し先のマンションの「顔認証システム」にはAさんの情報が登録されていなかったり、違うIDで登録されていたりします。
その結果、顔認証は突破できても（サーバーには接続できても）、いざ部屋に入ろうとすると「この鍵の持ち主は登録されていませんよ！」とエラーが出てしまうのです。この状態を**「孤立ユーザー」**と呼びます。
2ステップで解決！孤立ユーザーを救出するコマンド
解決策はシンプルです。引っ越し先の顔認証システムに、「この鍵は、この人のものですよ」と再登録してあげるだけです。
SQL Server Management Studio (SSMS) を使って、対象のデータベースに対して以下のクエリを実行しましょう。
ステップ1：不明になったユーザーを探す
まず、リストアしたデータベース内で、どのユーザーが不明（孤立）になっているかを確認します。
以下のコマンドを実行すると、紐付けが切れてしまったユーザーの一覧が表示されます。
-- 孤立しているユーザーを一覧表示
EXEC sp_change_users_login 'Report';‘Report’ という命令で、「問題があるユーザーを報告してください」とSQL Serverにお願いしているイメージです。
ステップ2：ユーザーとログインを正しく再接続する
ステップ1で見つかったユーザーを、同じ名前のログインに紐付け直します。例えば、‘test_user’ というユーザーが孤立していた場合、以下のコマンドを実行します。
-- 'Update_One'：一つのユーザーを更新する
-- 'test_user'：対象のDBユーザー名
-- 'test_user'：接続したいログイン名
EXEC sp_change_users_login 'Update_One', 'test_user', 'test_user';これで、‘test_user’ という鍵が、再び ‘test_user’ という顔認証データに正しく接続されました。アプリケーションから無事にログインできるはずです。
⚠️ 注意：このコマンドは将来廃止される予定ですsp_change_users_login は非常に便利なコマンドですが、マイクロソフトによると将来のバージョンではなくなる予定です。 SQL Server 2008以降では、 ALTER USER を使う方法が推奨されています。
-- 2008以降はこちら
ALTER USER [ユーザー名] WITH LOGIN = [ログイン名];とはいえ、sp_change_users_login は多くの環境でまだ利用できるため、覚えておくと非常に役立ちます。
まとめ
データベースのリストア後に発生するユーザーの不整合は、初めて遭遇すると少し戸惑うかもしれません。
しかし、原因が**「DBユーザー」と「ログイン」の紐付けが切れること**だと分かっていれば、落ち着いて対処できます。
- EXEC sp_change_users_login ‘Report’ で孤立ユーザーを確認
- EXEC sp_change_users_login ‘Update_One’, ‘ユーザー名’, ‘ログイン名’ で再接続
この2つのコマンドを覚えておくだけで、DBの移行作業がぐっとスムーズになります。ぜひ、あなたの備忘録にも加えてみてください。
↓↓↓★低価格でブログ初心者に最適★↓↓↓↓
コメント