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

ホーム＞技術情報＞ SQL Server 実践「SQL Server 2016 への移行とアップグレードの実践」

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

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

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

5.14　サーバーロール設定の移行

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

00436

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

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

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

00437

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

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

00438

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

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

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

00439

この 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 2016 上）で実行すれば、サーバーロールの設定を移行することができます。

なお、上記の画面は、SQL Server 2008 の場合ですが、SQL Server が利用している内部ユーザー（NT AUTHORITY\SYSTEM や NT SERVICE\MSSQLSERVER、NT SERVICE\SQLSERVERAGENT、SQL Server 2005 の場合は SQLServer2005MSSQLUser～ や、SQLServer2005SQLAgetUser～、BUILTIN\Administrators など）を sysadmin サーバーロールに追加するものも含まれてしまっているので、不要なものは取り除いて、移行先（SQL Server 2016 上）で実行するようにします。

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

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

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

00441

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

00442