アクセス許可の管理のための Azure SQL Database サーバー ロール

適用対象:Azure SQL Database

この記事では、Azure SQL Database の固定サーバー レベル ロールについて説明します。

概要

Azure SQL Database では、サーバーは論理的な概念であり、許可をサーバー レベルで付与することはできません。 アクセス許可の管理を簡素化するために、Azure SQL Database では、論理的なサーバー上のアクセス許可の管理に使用でいる、一連の固定サーバーレベルのロールが提供されています。 ロールとは、ログインをグループ化するセキュリティ プリンシパルです。

通常のユーザーが作成した他のプリンシパルと区別するため、これらの特別な固定サーバーレベルのロールには、プレフィックス ##MS_ とサフィックス ## が使用されています。

オンプレミスの SQL Server と同様に、サーバーのアクセス許可は階層的に編成されています。 これらのサーバーレベルのロールによって保持されるアクセス許可は、データベースのアクセス許可に反映されることがあります。 アクセス許可がデータベース レベルで効果的に役立つには、ログインがサーバーレベルのロール ##MS_DatabaseConnector## のメンバーである必要があります。このロールでは、すべてのデータベースに CONNECT を付与するか、個々のデータベースにユーザー アカウントを持っている必要があります。 これは、仮想 master データベースにも当てはまります。

たとえば、サーバー レベルのロール ##MS_ServerStateReader## はアクセス許可 VIEW SERVER STATE を持っています。 このロールのメンバーであるログイン ユーザーが、データベース master と WideWorldImporters でユーザー アカウントを持っている場合、このユーザーはそれら 2 つのデータベースに対して VIEW DATABASE STATE というアクセス許可を持つことになります。

Azure SQL Database では、現在、7 つの固定サーバー ロールが提供されています。 固定サーバー ロールに付与されるアクセス許可は変更できません。また、これらのロールに他の固定ロールをメンバーとして含めることはできません。 サーバーレベルのログインを、サーバーレベルのロールのメンバーとして追加することができます。

Azure SQL Database のログインとユーザーについては、「SQL Database、SQL Managed Instance、Azure Synapse Analytics へのデータベース アクセスを承認する」を参照してください。

固定サーバー レベル ロール

次の表に、固定サーバー レベル ロールとその機能を示します。

固定サーバー ロールのアクセス許可

固定サーバー レベルのロールごとに、特定の権限が割り当てられます。 次の表は、サーバーレベルのロールに割り当てられているアクセス許可を示しています。 また、ユーザーが個々のデータベースに接続できれば、継承されるデータベースレベルのアクセス許可も表示されます。

アクセス許可

サーバー ロールに他のログインを追加したり、サーバー ロールから他のログインを削除したりできるのは、サーバー管理者アカウントまたは Microsoft Entra 管理者アカウント (Microsoft Entra グループの場合もある) のみです。 これは Azure SQL Database に固有です。

サーバーレベルのロールの操作

次の表では、Azure SQL Database でサーバー レベルのロールを操作するためのシステム、ビュー、および関数について説明します。

例

このセクションの例では、Azure SQL Database のサーバーレベルのロールを使用する方法が示されています。

A. SQL ログインをサーバーレベルのロールに追加

次の例では、SQL ログイン Jiao をサーバーレベルのロール ##MS_ServerStateReader## に追加します。 このステートメントは、仮想 master データベースで実行する必要があります。

ALTER SERVER ROLE ##MS_ServerStateReader##
    ADD MEMBER Jiao;
GO

B: サーバーレベルのロールのメンバーであるプリンシパル (SQL 認証) の一覧の表示

次のステートメントでは、カタログ ビュー sys.server_role_members と sys.sql_logins を使用して、任意の固定サーバーレベルのロールのすべてのメンバーが返されます。 このステートメントは、仮想 master データベースで実行する必要があります。

SELECT sql_logins.principal_id AS MemberPrincipalID,
    sql_logins.name AS MemberPrincipalName,
    roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.sql_logins AS sql_logins
    ON server_role_members.member_principal_id = sql_logins.principal_id;
GO

C: 完全な例: サーバーレベルのロールへログインを追加し、ロール メンバーシップとアクセス許可のメタデータを取得し、テスト クエリを実行

パート 1: ロール メンバーシップとユーザー アカウントの準備

仮想 master データベースからこのコマンドを実行します。

ALTER SERVER ROLE ##MS_ServerStateReader## ADD MEMBER Jiao;

-- check membership in metadata:
SELECT IS_SRVROLEMEMBER('##MS_ServerStateReader##', 'Jiao');
--> 1 = Yes

SELECT sql_logins.principal_id AS MemberPrincipalID,
    sql_logins.name AS MemberPrincipalName,
    roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.sql_logins AS sql_logins
    ON server_role_members.member_principal_id = sql_logins.principal_id;
GO

結果セットは次のとおりです。

MemberPrincipalID MemberPrincipalName RolePrincipalID RolePrincipalName
------------- ------------- ------------------ -----------
6         Jiao      11            ##MS_ServerStateReader##

ユーザー データベースからこのコマンドを実行します。

-- Create a database-User for 'Jiao'
CREATE USER Jiao
FROM LOGIN Jiao;
GO

パート 2: ロール メンバーシップのテスト

ログイン Jiao としてサインインし、例で使用されているユーザー データベースに接続します。

-- retrieve server-level permissions of currently logged on User
SELECT * FROM sys.fn_my_permissions(NULL, 'Server');

-- check server-role membership for `##MS_ServerStateReader##` of currently logged on User
SELECT USER_NAME(), IS_SRVROLEMEMBER('##MS_ServerStateReader##');
--> 1 = Yes

-- Does the currently logged in User have the `VIEW DATABASE STATE`-permission?
SELECT HAS_PERMS_BY_NAME(NULL, 'DATABASE', 'VIEW DATABASE STATE');
--> 1 = Yes

-- retrieve database-level permissions of currently logged on User
SELECT * FROM sys.fn_my_permissions(NULL, 'DATABASE');
GO

-- example query:
SELECT * FROM sys.dm_exec_query_stats;
--> will return data since this user has the necessary permission

D: Microsoft Entra ログインのサーバー レベルのロールの確認

仮想 master データベースで次のコマンドを実行して、SQL Database のサーバー レベルのロールの一部であるすべての Microsoft Entra ログインが表示されます。 Microsoft Entra サーバーのログインの詳細については、Microsoft Entra サーバー プリンシパルに関する記事を参照してください。

SELECT member.principal_id AS MemberPrincipalID,
    member.name AS MemberPrincipalName,
    roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.server_principals AS member
    ON server_role_members.member_principal_id = member.principal_id
LEFT JOIN sys.sql_logins AS sql_logins
    ON server_role_members.member_principal_id = sql_logins.principal_id
WHERE member.principal_id NOT IN (
    -- prevent SQL Logins from interfering with resultset
    SELECT principal_id
    FROM sys.sql_logins AS sql_logins
    WHERE member.principal_id = sql_logins.principal_id
);

E. 特定のログインのための仮想 master データベース ロールの確認

仮想 master データベースで次のコマンドを実行し、bob が持っているロールを確認するか、または、その値を自分のプリンシパルに合わせて値を変更します。

SELECT DR1.name AS DbRoleName,
    ISNULL(DR2.name, 'No members') AS DbUserName
FROM sys.database_role_members AS DbRMem
RIGHT JOIN sys.database_principals AS DR1
    ON DbRMem.role_principal_id = DR1.principal_id
LEFT JOIN sys.database_principals AS DR2
    ON DbRMem.member_principal_id = DR2.principal_id
WHERE DR1.type = 'R'
    AND DR2.name LIKE 'bob%';

サーバーレベルのロールの制限事項

• ロールの割り当てが有効になるまでに、最大で 5 分かかる場合があります。 また、既存のセッションの場合、サーバー ロールの割り当てを変更しても、接続を閉じて再度開くまでは有効になりません。 これは、"master" データベースと、同じ論理サーバー上の他のデータベースとの間の分散アーキテクチャが原因です。

  • 部分的な回避策: 待機期間を短縮し、データベース内のサーバー ロールの割り当てが最新であることを確認するには、サーバー管理者または Microsoft Entra 管理者のアクセス許可を使用し、ログインがアクセスできるユーザー データベースで DBCC FLUSHAUTHCACHE を実行します。 現在ログオンしているユーザーは、メンバーシップの変更を有効にするために、DBCC FLUSHAUTHCACHE の実行後に再接続する必要があります。

• IS_SRVROLEMEMBER() は、master データベースではサポートされていません。

関連するコンテンツ

• データベース レベルのロール
• セキュリティ カタログ ビュー (Transact-SQL)
• セキュリティ関数 (Transact-SQL)
• 権限 (データベース エンジン)
• DBCC FLUSHAUTHCACHE (Transact-SQL)