Azure SQL Databaseで、ロールを制限したユーザーを作成する

ユーザー関連って最初にしか設定しないので、ふとした時に設定しようとすると「どうするんだっけ？」となるので...メモしておくことにしました。

ここ数年、BIブームが来てるとか来てないとかもあり、Readonlyのユーザーを作りたいとかバックアップだけのユーザーをつくりたいとかあると思います。
そんな時用のページです。

> Environment

Azure SQL Database　（2015/10時点）
SQL Server 2014SP1のManagement Studio

> Overview

SQLServer Management Studio（SSMS）を使って、スクリプトを実行してユーザーを作成するだけです。
手順は、ユーザーに直接ロールを付与したり、スキーマに付与したりといくつかありますが、今回は以下の手順で行います。

SQL Databaseのインスタンスに [ログイン] を作る
システムデータベースmasterに [ユーザー] を登録する
実際に利用したいカタログ（データベース）に [ユーザー] を登録する
ユーザーに [ロール]（権限）を付与する

> 事前準備として...

準備として、SSMSを開きます。
（SSMSの使い方については細かく書きませ〜ん）
そしてユーザーを作成するSQL Databaseのインスタンスに接続します。

SSMSについて余談ですが、2014のSP1を使ってない方はSP1に更新した方がよいです。
オンプレのSQL Serverに対してSSMSを利用したことがある方だと、SQL Databaseに対してSSMSを使うと一部の機能が利用できなくなってて不便と感じると思いますが、SP1では多少改善されてます。

> 1. SQL Databaseのインスタンスに[ログイン]を作る

SSMSで、[新しいクエリ]を開きます。
利用するカタログ（データベース）は[master]を選びます。
オンプレだとTransact-SQLので「use」を使って「use master」を使えればいいんですが、SQL Databaseではサポートされてないようでめんどくさいです。

（全然関係ないですが、「カタログ」って書くべきなのかわかりやすく「データベース」って書くべきなのか迷います）

新しく作るユーザー名は、「ContosoReadlonyUser」、パスワードは「Hogehoge2015」でつくります。
以下のスクリプトを実行します。

-- masterに対して実行
CREATE LOGIN ContosoReadlonyUser WITH PASSWORD='Hogehoge2015';

実行すると、オブジェクトエクスプローラーでSQL Serverのインスタンス > [セキュリティ] > [ログイン]の中に、ContosoReadlonyUserが追加されていることが確認できます。

（表示されないときは確認するフォルダーを右クリックして、[最新の情報に更新]します。）

> 2. システムデータベースの[master] テーブルに[ユーザー]を登録する

スクリプトを読むとそのままのことですが、1で作った[ログイン]からmasterに対して、[ユーザー]を作成します。

-- masterに対して実行
CREATE USER ContosoReadlonyUser FROM LOGIN ContosoReadlonyUser;

実行すると、オブジェクトエクスプローラーでSQL Serverのインスタンス >[データベース] > [システムデータベース] > [master] > [セキュリティ] > [ユーザー]の中に、ContosoReadlonyUserが追加されていることが確認できます。

SQL Server遣いの方はお馴染みのことですが、[セキュリティ]というフォルダーは、SQL Serverのインスタンス直下に一つ、それぞれのデータベースの中にひとつずつありますので、ご注意ください。

> 3. 実際に利用したいカタログ（データベース）に[ユーザー]を登録する

次は、自分の使うカタログ（データベース）に対してユーザーを作ります。ユーザーは、1で作った[ログイン]から作ります。
今回実行するカタログは、「ContosoDB」にするとします（スクリプト内には登場しませんが...）。

-- ContosoDBに対して実行
CREATE USER ContosoReadlonyUser FROM LOGIN ContosoReadlonyUser;

実行すると、オブジェクトエクスプローラーでSQL Serverのインスタンス >[データベース] > [ContosoDB] > [セキュリティ] > [ユーザー]の中に、ContosoReadlonyUserが追加されていることが確認できます
ちなみに、各データベースの中に作るユーザーの名称は、既存で存在してなければなんでも大丈夫ですが、敢えて変える必要もないかなーと思ってます。

> 4. ユーザーに[ロール]（権限）を付与する

今回は、Readonlyの権限です。その他のロールは後述します。

-- ContosoDBに対して実行
EXEC sp_addrolemember N'db_datareader', N'ContosoReadlonyUser';

これは、オブジェクトエクスプローラーからは確認できませんが、クエリで確認可能です。

-- ContosoDBに対して実行
SELECT 
    sp_role.name AS role_name, 
    sp_member.name AS member_name 
FROM 
    sys.database_role_members rm 
    LEFT JOIN 
        sys.database_principals sp_role 
    ON 
        rm.role_principal_id = sp_role.principal_id 
    LEFT JOIN 
        sys.database_principals sp_member 
    ON 
        rm.member_principal_id = sp_member.principal_id

このクエリは、ムッシュ先生のSEの雑記からの借用です。他にも参考になるクエリがたくさんありますので、必見です。
blog.engineer-memo.com

> 5. ロール情報、その他諸々

ロールについて、現時点ではここで確認できます。
https://msdn.microsoft.com/ja-jp/library/ms189121(v=sql.120).aspx

Azureは更新が速いので、設定するときに最新の情報を確認しましょう。
あと、リンクのMSDNのページはSQL Server向けのページなので、AzureのSQL Databaseでは使えないT-SQLもあります。ご注意を。

「テーブルごとに細かくわけるならスキーマ使った方がいいですよね～」とかあると思いますので、ロールの付与方法は臨機応変に。

SQL Databaseについてですが、
行/ページ圧縮が利用可能になったとか、行レベルのセキュリティも使えるようになったりで、より便利にDB側を設定できますね。機会があれば試してみたいなーと思います。
機能がどんどん増えてるので、随時SEの雑記でキャッチアップしないとなーと思います。
SQL Database で行/ページ圧縮が利用可能になったようです at SE の雑記

blog.engineer-memo.com

全然関係ないですが、Azure、「SQLデータベース」って翻訳しないで、「SQL Database」でよくない？と思う毎日でした。

読み方は...
SQLデータベースは「えすきゅーえるでーたべーす」で、
SQL Databaseは「スィィクゥェ..でーたべーす YEAH」
になるのかな...