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.13　サーバーロール設定の移行

サーバーロール（sysadmin や securityadmin、serveradmin など）は、ログインアカウントをグループ化するための機能なので、この設定も master データベース（システムデータベース）内に格納されています（以下の画面は SQL Server 2005）。

00465

したがって、サーバーロールの設定は、移行先で再設定をする必要があります。

◆ サーバーロール設定のスクリプト生成

サーバーロールの設定は、SQL Server 2005／2008／2008 R2 の標準のスクリプト生成機能だと、SQL Server 認証用のログインアカウントしかスクリプトを生成することができません（Windows 認証用のログインアカウントに関してはスクリプト生成ができません）。これは、次のような状況です（画面は SQL Server 2005 ですが、他のバージョンでも同様の操作です）。

00466

Management Studio の［表示］メニューから［概要］をクリックして概要ページを表示し（SQL Server 2008／2008 R2 の場合は［オブジェクトエクスプローラーの詳細］をクリック）、［ログイン］フォルダーで複数のログインアカウントを Ctrl キーを押しながら選択して、右クリックし、［名前を付けてスクリプト化］からスクリプト生成を行っています。

これで、ログインアカウントを作成する CREATE LOGIN ステートメントとともに、サーバーロールを設定するための sp_addsrvrolemember（サーバーロールへメンバーを追加するためのシステムストアドプロシージャ）が生成されるのですが、次のように SQL Server 認証用のログインアカウントに対してはうまく生成されるのですが、Windows 認証用のログインアカウントに関しては生成させることができません。

00467

Windows 認証用のログインアカウントに対しても、サーバーロールの設定をスクリプト化するには、次の 2つの方法があります。

server_role_members システムビュー（サーバーロールとログインアカウントの対応を確認しているビュー）から sp_addsrvrolemember を生成する
SQL Server 2014 の Management Studio をインストールしたマシンから、移行元の SQL Server へリモート接続をして、スクリプトを生成する（SQL Server 2012 のManagement Studio からでも同様の操作が可能）

1つ目の方法は、サーバーロールとログインアカウントの対応を確認することができる「server_role_members」システムビューを利用する方法です。このビューでは、次のようにサーバーロールとログインアカウントの対応を確認できます。

00468

この ID（principal_id）は、server_principals システムビューと JOIN することで、サーバーロールの名前やログインアカウントの名前を取得することができるので、次のようにサーバーロールを設定するためのスクリプトを生成することができます。

SELECT 'EXEC sp_addsrvrolemember ''' + r2.name + ''',''' + r1.name + ''''
FROM sys.server_role_members m
INNER JOIN sys.server_principals r1 ON r1.principal_id = m.role_principal_id
INNER JOIN sys.server_principals r2 ON r2.principal_id = m.member_principal_id

この生成された sp_addsrvrolemember システムストアドプロシージャを、移行先（SQL Server 2014 上）で実行すれば、サーバーロールの設定を移行することができます。なお、上記の画面は、SQL Server 2005 の場合ですが、SQL Server 2005 が利用している内部ユーザー（SQLServer2005MSSQLUser～や SQLServer2005SQLAgetUser～ ログインアカウント）、SQL Server 2005 のインストール時に自動追加されるシステムアカウント（NT AUTHORITY\SYSTEM）、BUILTIN\Administrators グループなどを sysadmin サーバーロールへ追加するものが含まれてしまっているので、これらは SQL Server 2014 上では実行する必要はありません（sysadmin サーバーロールへ追加する必要はありません）。

SQL Server 2014 の Management Studio を利用する方法

サーバーロールの設定をスクリプト化する 2つ目の方法は、SQL Server 2014 の Management Studio をインストールしたマシンから、移行元の SQL Server へリモート接続をして、スクリプトを生成します。SQL Server 2014（または SQL Server 2012）の Management Studio を利用すれば、Windows 認証用のログインアカウントに関しても、サーバーロールの設定をスクリプト化することができます。

これを行うには、SQL Server 2014 の Management Studio で［表示］メニューから［オブジェクトエクスプローラーの詳細］をクリックして詳細ページを表示し、次のように［ログイン］フォルダーで複数のログインアカウントを Ctrl キーを押しながら選択して、右クリックし、［ログインをスクリプト化］から、スクリプト生成を行います。

00470

これで、ログインアカウントを作成する CREATE LOGIN ステートメントとともに、サーバーロールを設定するための ALTER SERVER ROLE ステートメントが生成されます（SQL Server 2012 以降では、sp_addsrvrolemember の代わりにこのステートメントでサーバーロールへのメンバーの追加ができるようになりました）。

00471