SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Database で VIEW SERVER STATE が必要なログインを複数作成する

without comments

Simple Monitor では、SQL Database の情報取得について、「sys.dm_os_performance_counters」を使用して情報を取得しているのですが、この DMV を参照するためには「VIEW SERVER STATE」の権限が必要となります。

今までは、「サーバー管理者」のログインを使用していたため、サクッと情報が取得できていたのですが、個別に作ったログインを使用して上記の DMV の情報を取得しようとした際にうまくいかず、「サーバー管理者」以外のログインで、情報の取得を使用とした際のメモを。
結論から言うと、自分で作ったログインでは、上記の DMV を参照することはできなかったのですが、DMV が参照可能なログインを複数用意することはできました。

SQL Database のログイン管理については、データベース アクセスの制御と許可 に記載されています。
この中で「dbmanager」というロールが紹介されているのですが、このロールにログインを含めても、そのログインでは「VIEW SERVER STATE」の権限を付与することがはできませんでした。
「sys.dm_db_」で始まる DMV に関しては、「VIEW DATABASE STATE」の権限を付与することで対応ができますので、これらの DMV を検索したい場合には、

GRANT VIEW DATABASE STATE TO <ユーザー名>

で権限を付与することができます。
これを「GRANT VIEW SERVER STATE」に変更しても、SQL Database では、サーバーレベルの権限付与が対応しておらず、エラーとなり個別の権限を付与することができません。
sys スキーマや、DMV に直接権限を付与しようとしても、想定している動作にすることができず、SQL Database のサーバーを作成した際に設定をする「サーバー管理者」以外のログインで DMV を検索させようとすると、権限周りでかなり悩ましいことになるようです…。
現状の回避方法の一案としては「Active Directory 管理者」を設定することになるのかと思います。
Active Directory 管理者は、サーバー管理者と同様の権限が付与された状態となりますが、「Azure Active Directory のグループ」を指定することが可能です。
これによりグループ内のユーザーが「サーバー管理者」と同様の権限が付与された状態となりますので、「VIEW SERVER STATE」の権限が付与されたログインを複数作成することが可能となります。
最新の「Invoke-SqlCmd」であれば、Azure Active Directory を使用した認証に対応しています。
クライアント アプリケーションからの Azure AD の ID を使用した接続
Invoke-SqlCmd では、接続文字列を使用した、接続もサポートされていますので、以下のような実行をすることで、AAD のユーザーを使用して、SQL Database に接続を行うことができます。

$connectionstring = "Server=<サーバー名>.database.windows.net;Database=<db 名>;Authentication=Active Directory Password;User Id=<aad ユーザー>;Password=<aad パスワード>"
Invoke-Sqlcmd -ConnectionString $connectionstring -Query "SELECT @@VERSION"

 
SQL Server であれば、VIEW SERVER STATE の権限付与は、サクッとできるのですが、SQL Database / SQL DW で「sys.dm_os_」で始まる DMV を使用する場合は、権限周りは意識しておかないといけないですね。

Written by Masayuki.Ozawa

4月 19th, 2017 at 10:56 pm

Posted in SQL Database

Tagged with

Leave a Reply