SQL Server パフォーマンスチューニング、コンサルティング、アドバイス、相談、定期診断、トレーニング

ホーム＞技術情報＞ SQL Server 2014 実践 No.2 SQL Server 2014 への移行とアップグレードの実践

SQL Server 2014 実践シリーズ（HTML 版）
「No.2 SQL Server 2014 への移行とアップグレードの実践」

松本美穂と松本崇博が執筆した SQL Server 2014 実践シリーズの「No.2 SQL Server 2014 への移行とアップグレードの実践」の HTML 版です。日本マイクロソフトさんの Web サイトで Word または PDF 形式でダウンロードできますが、今回、HTML 版として公開する許可をいただきましたので、ここに掲載いたします。[2015年12月29日]

目次へ | 前のページへ | 次のページへ

5.12　ログインアカウントの移行（DB ユーザー、オブジェクト権限）

データベースユーザーは、ログインアカウントと紐付いている（マッピングされている）ので、ログインアカウントの移行を行わないと、データベースユーザーを利用することができません。また、データベースユーザーに紐付いたオブジェクト権限（テーブルやビューに対する SELECT 権限や INSERT 権限、ストアドプロシージャに対する EXECUTE 権限など）も無力になってしまいます。

ログインアカウントとのマッピングが切れたデータベースユーザーは、「不明なデータベースユーザー」や「孤立ユーザー」と呼ばれ、次のように表示されます。

00438

これは、SQL Server 認証用のログインアカウントである「sqllogin1」に対応したデータベースユーザーのプロパティを開いている画面で、［ユーザーの種類］が「ログインを持たない SQL ユーザー」になっています。データベースユーザーに対応したログインアカウントが存在しない場合（マッピングが切れている場合）には、このように表示されます。この状態では、sqllogin1 ユーザーはデータベースにアクセスすることができません。

Windows 認証用のログインアカウント（Active Directory ドメインユーザーや Windows ローカルユーザーに対応したログインアカウントの場合）は、次のように［ユーザーの種類］が「Windows ユーザー」と表示されて、［ログイン名］が "空" で表示されるものが、不明なデータベースユーザーとなっていて、利用することができません。

00439

なお、SQL Server 認証用のログインアカウントとのマッピングが切れたユーザー（不明なデータベースユーザー）は、次のように sp_change_users_login というシステムストアドプロシージャを利用して、リストアップすることもできます。

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

このような不明なデータベースユーザーを正しく利用できるようにするには、移行元と移行先で、同一のログインアカウントを作成しておく必要があります。作成方法／注意点は、Windows認証用のログインアカウントである Active Directory ユーザーまたは Windows のローカルユーザーの場合と、SQL Server 認証用のログインアカウントの場合で異なるので、それぞれごとに説明します。

00441

◆ Active Directory ユーザー（ドメインユーザー）の場合

ログインアカウントが Windows 認証用で、かつ Active Directory ドメインユーザー、移行元と移行先が同じドメインに所属している場合には、同一のログインアカウントを簡単に作成することができます。標準の「スクリプト生成」機能や、後述の「sp_help_revlogin ストアドプロシージャ」、「データベース コピーウィザード」、「Integration Services のログイン転送タスク」などを利用して、ログインアカウントを再作成または転送すれば、同一のログインアカウントを作成できるので、不明なデータベースユーザーを簡単に解消することができます（データベースユーザーを移行元とまったく同じように利用できるようになります）。

標準の「スクリプト生成」機能を利用する場合

標準の「スクリプト生成」機能を利用する場合は、次のように、移行元の Management Studio で、スクリプト化をしたいログインアカウントを右クリックして、［名前を付けてログインをスクリプト化］の［CREATE］から［新しいクエリエディタウィンドウ］をクリックします（画面は SQL Server 2005 の場合ですが、他のバージョンでも同じように操作できます）。

00442

これで CREATE LOGIN ステートメントが生成されるので、このスクリプトを移行先（SQL Server 2014 上）で実行すれば、同じログインアカウントを作成することができます。

移行先に同じログインアカウントを作成しておけば、次のようにデータベースユーザーとログインアカウントのマッピング（紐付け）が復活して、データベースユーザーおよびオブジェクト権限を移行元と同じように利用できるようになります。

00443

このように、ログインアカウントが Active Directory ユーザーで、同じドメイン内の別マシンへ移行する場合には、同一のログインアカウントを作成するだけで、データベースユーザーとオブジェクト権限を利用できるようになります。

もし、移行元と移行先が異なるドメインに所属していたり、別のワークグループ環境の別マシンに復元したりする場合には、次のように CREATE LOGIN ステートメントの実行時にエラーになります。

00444

ドメインユーザーに対応したログインアカウントの作成は、あくまでも同じドメイン内に所属している場合にのみ行うことができます。

Tips：複数のログインアカウントをまとめてスクリプト化したい場合
移行元で、複数のログインアカウントをまとめてスクリプト化したい場合は、Management Studio で［表示］メニューから［概要］をクリックして概要ページを表示し、複数のログインアカウントを Ctrl キーを押しながら選択／右クリックします（SQL Server 2005 の場合）。

これでまとめてスクリプト化できるので、ログインアカウントが多い場合に便利です。
SQL Server 2008／2008 R2／2012 の場合は、［表示］メニューから［オブジェクトエクスプローラーの詳細］をクリックして詳細ページを表示することで、同様の操作ができます。

◆ Windows のローカルユーザーの場合

ログインアカウントが Windows のローカルユーザーの場合は、移行先（SQL Server 2014 上）でデータベースユーザーを再作成する必要があります。これには、復元したデータベースユーザーをいったん削除して、手動で同じデータベースユーザーを作り直さなければなりません。したがって、データベースユーザーへ設定したオブジェクト権限についても再設定しなければなりません。

この理由は、Windows のローカルユーザーの場合は、同じ Windows ユーザー（OS 上のユーザーアカウント）を作り直しても、内部的に生成される SID（OS 上のユーザーを識別する Security ID）が異なるものが割り当てられるためです。

00446

このように、データベースユーザーは、内部的には、ログインアカウントの名前とではなく、SID とマッピングされているので、Windows のローカルユーザーの場合は、復元後のデータベースユーザーとログインアカウントをマッピングすることができません（SID が異なる場合には、不明なデータベースユーザーを解消することができません）。

なお、前述の Active Directory ユーザー（ドメインユーザー）の場合に、同一のログインアカウントを作成するだけで、簡単に再マッピングできたのは、SID が同じものを利用できるためです。同じドメインに所属していれば、共通（同一の SID）のドメインユーザーを利用することができます。

したがって、Windows ローカルユーザーを利用していて、移行先でも同じ名前の Windows ローカルユーザーを利用したい場合には、手動ですべてを再作成するか、データベースユーザーやオブジェクト権限を移行元でスクリプト化しておくようにします（詳しくは後述します）。

復元したデータベース ユーザーの削除

再作成にあたっては、移行先（SQL Server 2014 上）で、復元したデータベースユーザーをいったん削除する必要がありますが、これは Management Studio で［表示］メニューから［オブジェクトエクスプローラーの詳細］をクリックして「詳細」ページを表示することで、次のように複数のデータベースユーザーをまとめて削除することができるので便利です。

00447

データベース ユーザーとオブジェクト権限のスクリプト化（スクリプト生成ウィザード）

移行元で、ログインアカウントやデータベースユーザー、オブジェクト権限をスクリプト化するには、スクリプト生成ウィザードを利用すると便利です。このウィザードを利用するには、次のようにデータベースを右クリックして、［タスク］メニューの［スクリプトの生成］をクリックします（画面は SQL Server 2005 ですが、他のバージョンでも同じように操作できます）。

00448

スクリプト生成ウィザードでは、次のように［スクリプトオプションの選択］ページで、［オブジェクトレベル権限のスクリプトを作成］と［スクリプトログイン］を「True」へ変更することで、データベースユーザーとオブジェクト権限、データベースユーザーに対応したログインアカウントをスクリプト化することができます（SQL Server 2005／2008 の場合）。

00449

SQL Server 2008 R2／2012 の場合は、次のように操作します。

00450

次の［オブジェクトの種類を選択］ページでは、データベースユーザーをスクリプト化するために［ユーザー］をチェックし、テーブルに対するオブジェクト権限をスクリプト化する場合には［テーブル］をチェックします。ビューやストアドプロシージャに対するオブジェクト権限をスクリプト化するには、それらもチェックしておきます。

00451

［テーブルの選択］ページでは、オブジェクト権限をスクリプト化したいテーブルをチェックします（画面は Products テーブルをチェック。［すべて選択］ボタンをクリックした場合は、すべてのテーブルを選択することができます）。これらの画面は、SQL Server 2005／2008 の場合ですが、SQL Server 2008 R2／2012 の場合は、ウィザードの最初のページで、ユーザーやテーブルを選択することができます。

SQL Server 2005／2008 の場合は、次の［ユーザーの選択］ページで、スクリプト化したいデータベースユーザーを選択します。

00452

［出力オプション］ページで、［スクリプトを新しいクエリウィンドウに保存］を選択すれば、クエリエディターにスクリプトを生成することができます。あとは、次のように［完了］ボタンをクリックすれば、スクリプト生成が開始されます。

00453

00454

テーブルを作成するスクリプト（CREATE TABLE）などが余分に出力されてしまいますが、ログインアカウントを作成するスクリプト（CREATE LOGIN）や、データベースユーザーを作成するスクリプト（CREATE USER）、オブジェクト権限を設定するスクリプト（GRANT、DENY など）が生成されていることを確認できると思います。Windows のローカルユーザーの場合は、「サーバー名\ユーザー名」形式の名前になるので、サーバー名の部分を移行先のサーバー名へ変更すれば、移行先で同じユーザー、同じオブジェクト権限を再設定できるようになります。

◆ SQL Server 認証用のログインアカウントの場合

ログインアカウントが SQL Server 認証用の場合は、SQL Server 標準のツール（スクリプト生成機能や、データベースコピーウィザード、ログイン転送タスクツールなど）では、同一のログインアカウントを作成することができないことに注意する必要があります。標準ツールでは、パスワードと SID（SQL Server 認証用のログインアカウントに内部的に割り当てられた Security ID）を生成／複製することができません（パスワードとSIDは、移行元と移行先で異なるものが生成されてしまいます）。

SQL Server 認証用のログインアカウントの場合にも、Windows 認証用と同様、データベースユーザーは、SIDとマッピングされているので、SID が異なる場合には、不明なデータベースユーザーを解消することができません。

00455

不明なデータベースユーザーを解消するには（データベースユーザーとログインアカウントを再マッピングするには）、移行先（SQL Server 2014 上）で、同じ名前／パスワードのログインアカウントを作成した後に、次のように ALTER USER ステートメントを実行します。

USE データベース名
ALTER USER 不明なデータベースユーザーの名前
WITH LOGIN = 移行先に作成した新しいログインアカウントの名前

このように、ALTER USER ステートメントを実行すると、移行先で作成した新しいログインアカウントと、復元したデータベースユーザーを再マッピングすることができます（移行元の古い SID に紐付いていたものを、移行先の新しい SID へ紐付くように更新することができます）。

再マッピングが成功すると、データベースユーザーのプロパティは、次のように表示されます。

00457

なお、ALTER USER ステートメントの代わりに、次のように sp_change_users_login ストアドプロシージャを実行しても、再マッピングを行うことができますが、このストアドプロシージャは将来のバージョンでは削除される予定です。

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

ALTER USER ステートメントで不明なデータベースユーザーの再マッピングを行う方法は、データベースユーザーごとに実行する必要があるので、データベースユーザーの数が多い場合には、大変になります（同じパスワードのログインアカウントを作成する手間もかかります）。これらを解決してくれる方法が、次に説明する sp_help_revlogin ストアドプロシージャになります。これを利用すれば、同じ名前／パスワードかつ、同じ SID のログインアカウントを一括で作成することができます。

同じ名前／パスワードで、同じ SID のログイン アカウントの作成： sp_help_revlogin

sp_help_revlogin は、同じ名前／パスワードで、同じSIDのログインアカウントを一括で作成することができる大変便利なストアドプロシージャです。移行元と移行先で、同じ SID のログインアカウントを移行先で作成すれば、前述の ALTER USER ステートメントを実行しなくても、不明なデータベースユーザーを解消することができるからです。

sp_help_revlogin ストアドプロシージャは、マイクロソフトのサポート技術情報（KB：Knowledge Base）の文書番号 918992 で提供されています。

SQL Server 2005 のインスタンス間でログインおよびパスワードを転送する方法
http://support.microsoft.com/kb/918992

この文書で提供されるスクリプトを、次のように丸ごとコピーして（手順２の USE master からスクリプトの最後までを選択してコピー）、移行元の SQL Server のクエリエディターから実行すれば、sp_help_revlogin ストアドプロシージャを作成することができます。

00458

00459

画面は SQL Server 2005 ですが、このスクリプトは SQL Server 2008／2008 R2／2012 でも同じように利用することができます。

これにより、master データベース内に sp_help_revlogin ストアドプロシージャが作成されるので、次のように実行すれば、同一のパスワード／SIDのログインアカウントを作成するためのスクリプトを生成することができます。

USE master
EXEC sp_help_revlogin

実行結果には、CREATE LOGIN ステートメントが生成されて、WITH PASSWORD= や SID= 付きのスクリプトが生成されて、同じパスワードおよび同じ SIDのログインアカウントを作成できるようになります。

なお、このスクリプトには、Windows 認証用のログインアカウントを作成するものや、SQL Server 2005 が利用している内部ユーザー（SQLServer2005MSSQLUser～や、SQLServer2005SQLAgetUser～ ログインアカウント）、SQL Server 2005 のインストール時に自動追加されるシステムアカウント（NT AUTHORITY\SYSTEM）、BUILTIN\Administrators グループなどを作成するための CREATE LOGIN ステートメントも生成されるので、必要なログインアカウントのみを選択して、移行先（SQL Server 2014 上）で実行するようにします。

00461

このように、移行元と移行先で、同じ SID のログインアカウントを作成すれば、不明なデータベースユーザーが解消されて、データベースの復元後にも、データベースユーザーやオブジェクト権限をそのまま利用することができます。

00462

◆ データベースロールのスクリプト化

データベースロールを利用して、データベースユーザーをグループ化している場合には、データベースロールの設定もスクリプト化しておく必要があります。

00463

前述のスクリプト生成ウィザードでは、データベースロールの設定をスクリプト化することができないので、データベースロールのメンバー情報を参照できる database_role_members システムビューとデータベースユーザーの情報を参照できる database_principals システムビューを、次のように利用します。

SELECT 'EXEC sp_addrolemember ''' + p1.name + ''', ''' + p2.name + ''''
FROM sys.database_role_members rm
INNER JOIN sys.database_principals p1 ON rm.role_principal_id = p1.principal_id
INNER JOIN sys.database_principals p2 ON rm.member_principal_id = p2.principal_id