この記事で出てくるパスワードとかSIDを指定している環境は存在しないので安心してください。
AzureのユーザとDBのユーザ
Portalで作成できるユーザは管理者のみです。それ以外のユーザはDBに接続して作成する必要があります。
AzureのロールもDB内のロールとは関係ありません。
SQL DatabaseとManaged Instanceで利用できる認証方法
以下の二つの認証方法があり、このどちらも利用可能な状態（混合モード）が既定値です。
- SQL 認証
- Azure Active Directory 認証
Azure Active Directory 認証には以下のバリエーションがあります。
- マネージドID…Azure VM 上のアプリケーションで利用できる
- 統合認証…Azure 以外のドメイン参加済みマシン上のアプリケーションで利用できる
- 対話型…人間がSSMSなどの管理ツールを使う場合
- サービスプリンシパル
サービスプリンシパルを利用するならSQL認証でいいような気もしますが、IDの管理をAzure ADに集約したい場合で、マネージドIDが利用できないケース（AKS上のアプリケーションなど）は選択肢になると思います。
ログインとユーザー
SQL Serverにはログインという概念があります。ログインはデータベースではなくサーバに紐づくオブジェクトです。
一方、ユーザはデータベースに紐づくオブジェクトで、ログインから派生させるような形でユーザを作ります。
たとえば以下のようにログインを作った場合、
CREATE LOGIN fouser WITH PASSWORD='jCkGu3SwPk3ikMyC'
以下のようにログインを指定してユーザを作ります。
USE <データベース名>
CREATE USER fouser FROM LOGIN fouser
包含データベースユーザ
ログインを作らずに、直接DBにユーザのみを作る方法もあります。この方法で作ったユーザを包含データベースユーザと言います。
USE <データベース名>
CREATE USER fouser3 WITH PASSWORD='jCkGu3SwPk3ikMyC'
こちらの方が直感的だと思いますし、SQL DatabaseでAzure ADで認証する場合は包含データベースユーザになってしまいます（Managed Instanceの場合はAzure AD サーバー プリンシパル (ログイン) がサポートされています）。
USE <データベース名>
CREATE USER [bob@contoso.com] FROM EXTERNAL PROVIDER;
フェイルオーバーグループを構成する場合の注意点
フェイルオーバーグループを構成する場合は以下のいずれかの方法でユーザーを作る必要があります。
- 包含データベースユーザ
- セカンダリのログイン作成時にプライマリのSIDと同じものを指定する
ログイン作成時にSIDを合わせていないと、以下のようなエラーが出てしまいます。
SIDの確認方法は以下です。
SELECT m.name, m.sid, r.name FROM sys.database_role_members rm
LEFT OUTER JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
LEFT OUTER JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id
WHERE m.name = 'fouser'
以下のようにSIDを指定することができます。
CREATE LOGIN fouser WITH PASSWORD='jCkGu3SwPk3ikMyC', SID = 0x0106000000000064000000000000000027844909910D6D43B4C8F208B6D43FD4
デプロイ時に作成されるユーザ
デプロイ時に作成されるユーザはSQL 認証のサーバー管理者になっています。
SQL DBの場合はAzure SQL Database 論理サーバーのサーバー レベルのプリンシパルで、Managed Instanceの場合はsysadmin サーバー ロールのメンバーです
Azure Active Directory のみの認証を構成すると、既存のSQL認証のユーザは残りますが、論理サーバーとインスタンスに接続できるのは、Azure Active Directory ベースのログインとユーザーのみという状態になります。
ロール
デフォルトで以下のロールが存在していて、これとは別にCREATE ROLEを使って独自のロールを作ることもできます。
| ロール | 説明 |
|---|---|
| public | データベース ユーザーはすべて publicに属しています。これを意識しないといけない場面が分からず… |
| db_owner | データベースでのすべての構成作業とメンテナンス作業を実行できます。 |
| db_securityadmin | カスタム ロールのロール メンバーシップの変更、権限の管理を実行できます。 |
| db_accessadmin | Windows ログイン、Windows グループ、SQL Server ログインのデータベースに対するアクセスを追加または削除できます。 |
| db_backupoperator | データベースをバックアップできます。※SQL Databaseでは関係ないです |
| db_ddladmin | DDLをデータベースで実行できます。 |
| db_datawriter | ユーザー テーブルのデータを追加、削除、または変更できます。 |
| db_datareader | ユーザー テーブルとビューからすべてのデータを読み取ることができます。 |
| db_denydatawriter | ユーザー テーブルのデータを追加、変更、または削除することができなくなります。 |
| db_denydatareader | ユーザー テーブルとビューのデータを読み取ることができなくなります。 |
| dbmanager | SQL DatabaseとSynapseで出てくるロールです。データベースの作成と削除を行うことができます。 データベースを作成する dbmanager ロールのメンバーは、そのデータベースの所有者になります。 |
| loginmanager | SQL DatabaseとSynapseで出てくるロールです。仮想 master データベースのログインを作成および削除できます。 |
ユーザにロールを付与する場合は、以下のようにロールのメンバーにするという形になります。
ALTER ROLE db_datareader ADD MEMBER fouser2
ユーザからロールをはく奪する場合は以下です。
ALTER ROLE db_datareader DROP MEMBER fouser2
EXECUTE AS LOGIN, EXECUTE AS USER
EXECUTE AS LOGINとEXECUTE AS USERはsudoみたいなことができるようにするためのもので、一時的に他人になれる仕組みです。
REVERTをすると元のログインもしくはユーザに戻ります。
この仕組みの利用シーンとしては以下の3つの場合があると思っています（私の想定なだけでMSの見解として出ているわけではないです）。
- 普段から強すぎる権限を与えられた状態で作業をするのではなく、必要なときにログインやユーザを切り替えて作業する場合
- 管理者がログインやユーザを作成した際に、付与した権限が意図したものと一致しているかを確認する
EXECUTE AS LOGINとEXECUTE AS USERを使って他人になるためには以下の二つの条件を満たす必要があります。
- 指定のログインやユーザに対する IMPERSONATE 権限がある
- 指定のログインやユーザ側でIMPERSONATE ANY LOGIN 権限が拒否されていない
WITHOUT LOGINを指定すると、ログインと紐づかず、パスワードもなく、EXTERNAL PROVIDERで管理されているわけでもないユーザを作ることができます。
これを使ってまず2つユーザーを作ってみましょう。
CREATE USER user1 WITHOUT LOGIN;
CREATE USER user2 WITHOUT LOGIN;
user1にuser2に成り代わる権限を付与します。
GRANT IMPERSONATE ON USER:: user2 TO user1;
まずはuser1になってみましょう。
EXECUTE AS USER = 'user1';
SELECT SUSER_NAME(), USER_NAME();
成功しました。
次にuser2になってみましょう。
EXECUTE AS USER = 'user2';
SELECT SUSER_NAME(), USER_NAME();
成功しました。
この状態でuser1になってみましょう。
EXECUTE AS USER = 'user1';
SELECT SUSER_NAME(), USER_NAME();
権限がないので怒られます。
REVERTをすると元のログインもしくはユーザに戻ります。