SQL Server でユーザー ロールを確認する方法

Netwrix Auditor for SQL Server

1. Netwrix Auditor を開き、Reports -> Predefined -> SQL Server - State-in-Time -> SQL Server の Account Permissions に移動します。
2. 次のフィルターを指定します：
   • 「User account」フィルターで、ユーザーのフル ネームを入力します（例：MILKYWAY/TomSimpson）。
   • 「Object type」フィルターで、Server Instance, Database を選択します。
3. 「View 」をクリックして、ユーザーの有効な権限を明確に示すレポートを作成します。

ネイティブ ソリューション

ユーザーの SQL Server ロールを一覧表示する

1. 開始 Microsoft SQL Server Management Studio (MSSMS)。
2. メニューの File から「Connect Object Explorer」をクリックします。
3. Connect to Server ダイアログ ボックスで、次の設定を指定します:
   • 「サーバーの種類」リスト ボックスで「Database Engine」を選択します。
   • 「サーバー名」テキスト ボックスに、SQL クラスター サーバーの名前を入力します。
   • 「認証」リスト ボックスで「SQL Server Authenticationmethod」を選択し、使用する資格情報を指定します。サーバーに接続するたびにパスワードを再入力したくない場合は、「Remember password」にチェックを入れます。
4. 「Connect」をクリックします。
5. 「Execute」をクリックします（または「F5」キーを押します）。
6. 接続したら、New Query をクリックし、次のスクリプトをクエリ フィールドに貼り付けます:

      select r.name as Role, m.name as Principal

from

    master.sys.server_role_members rm

    inner join

    master.sys.server_principals r on r.principal_id = rm.role_principal_id and r.type = 'R'

    inner join

    master.sys.server_principals m on m.principal_id = rm.member_principal_id

where m.name = 'MILKYWAY\TomSimpson'
      

• クエリの実行結果にある、サーバー レベルのロールとプリンシパル（メンバー名）の一覧を確認します:

ユーザー向けに SQL Server でデータベース ロールを問い合わせる

1. 起動してMicrosoft SQL Server Management Studio (MSSMS)。
2. ファイル メニューで Connect Object Explorer をクリックします。
3. Connect to Server ダイアログ ボックスで、次の設定を指定します：
   • Server type の一覧ボックスでDatabase Engine を選択します。
   • Server name のテキスト ボックスに、SQL クラスタ サーバーの名前を入力します。
   • Authentication の一覧ボックスでSQL Server Authentication method を選択し、使用する資格情報を指定します。サーバーに接続するたびにパスワードを再入力したくない場合は、Remember password にチェックを入れます。
4. Connect をクリックします。
5. クリック Execute (または F5 キーを押します)。
6. 接続したら、ユーザーのロールを問い合わせる Database を選択します。
7. クリック New Query を選択し、次のスクリプトをクエリ欄に貼り付けます：

      SELECT r.name role_principal_name, m.name AS member_principal_name

FROM sys.database_role_members rm 

JOIN sys.database_principals r 

    ON rm.role_principal_id = r.principal_id

JOIN sys.database_principals m 

    ON rm.member_principal_id = m.principal_id

where m.name = 'MILKYWAY\TomSimpson'
      

• クエリ実行結果に表示された、サーバーレベルのロールとプリンシパル（メンバー名）の一覧を確認します：
  

SQL Server でユーザーのロールを、1 つもクエリを使わずに確認する方法を見つけましょう。

Microsoft SQL Server では、データベース管理者が構造化データへの権限を管理できるようにロールが用意されています。サーバーレベルのロールは、名前のとおりサーバー全体へのアクセスを許可し、Windows の世界のグループと似た仕組みです。さらに、各 SQL データベースにも固有の権限とロールを設定できます。

セキュリティを維持し、PCI DSS や HIPAA を含む多くの規制に準拠するには、各ユーザーに割り当てられているすべてのサーバー ロールとデータベース ロールを把握する必要があります。内容が複雑なため、手元にネイティブ ツールしかない場合は、その作業はかなり大変です。

まず、サーバーロール、権限、ユーザー認証情報、依存関係などのサーバーレベルの設定は master データベースに保存されます。sys.server_principals のシステム ビューを使用します。,

• SQL Server でユーザーとサーバー ロールを一覧表示するには、sys.server_principals のようなシステム ビューをクエリできます。
• SQL Server のデータベースに関するユーザーとロールを一覧表示するには、sys.database_principals のようなシステム ビューをクエリできます。

ストアド プロシージャはサーバーの一部領域の管理に役立ちますが、カスタム レポートを作成するにはクエリを使用する必要があります（たとえば、特定の列名で複数のテーブルを照合するレポートなど）。たとえば、サーバー レベルのロール メンバーシップ情報は、master データベースの server_role_members システム ビューに保存されています。principal（主体）の ID はリンクされているため、ID 番号を基準に sys.server_principals と master.sys.server_role_members を結合するクエリで、SQL Server のユーザー ロールの概要を取得できます。ユーザーは固定サーバー ロール（fixed server roles）の各メンバーについて、自分のサーバー ロール メンバーシップと principal ID を表示できますが、すべてのサーバー ロール メンバーシップを表示するには追加の権限、または security admin の固定サーバー ロールへのメンバーシップが必要になることを忘れないでください。

データベース レベルの情報を収集するには、各データベースごとに SQL Server のデータベース ロールを個別にクエリする必要があり、手間がかかることがあります。さらに、ロールはネスト（入れ子）にできます。つまり、データベース ユーザー、アプリケーション ロール、その他のデータベース ロールが、データベース ロールのメンバーになり得ます。

要するに、ネイティブ ツールで現在のユーザー ロールに関する包括的な情報を取得することは、複雑でかなり疲れる作業になりがちです。 一方で Netwrix Auditor を使えば、数回のクリックで、誰がどのサーバーおよびデータベース ロールを保持しているかを読みやすい形式で詳細に確認できます。製品に含まれるリンク済みレポートにより、専門家は、サーバー全体にわたるネストされたユーザー ロールのメンバーシップのややこしさを素早く整理でき、調査が効率化されます。必要な重要な情報はすべて得られます。つまり、ユーザーがアクセスできるすべてのオブジェクトの一覧（そのパスとオブジェクトの種類を含む）、付与された権限、権限が付与された方法（直接、ロール メンバーシップ経由など）、およびそれが明示的か継承によるものかどうかです。アカウント、オブジェクト、サーバーの各レベルを含め、さまざまな観点から権限を分析できます。これらすべての情報は、スクリプトを書く手間、Excel への情報のエクスポート、そして手作業での確認という煩雑さなしに提示され、利用可能です。