SQL Server サーバー・データベースロールとその設定方法

SQL Server では、ユーザーの権限を管理を簡単にするために、固定サーバーレベルのロールと、データベースレベルのロールが用意されています。

ここでは、固定サーバーレベルのロールと、データベースレベルのロールと設定方法についてご説明します。

固定サーバーレベルのロールとは？

固定のサーバーレベルのロールとは、サーバーに対しての権限をまとめたものです。

ログインユーザーに対してサーバーロールを設定することで、ひとつひとつサーバーに対してできる権限を許可していくのではなく、まとまった権限を与えることができます。

ログインユーザーに複数のサーバーロールを設定したり、ユーザー定義のサーバーロールを作成して設定することも可能です。

なんでもできる固定サーバーロールは sysadmin で、一番権限がないのが public です。

他には serveradmin、securityadmin、processadmin、setupadmin、bulkadmin、diskadmin、dbcreator があります。

マイクロソフトのサイトに、サーバーロールとパーミッションの関係を示した、わかりやすい表がありましたので、載せておきます。

[Reference] Server-Level Roles - SQL Server | Microsoft Docs

固定のデータベースレベルのロールとは？

固定のデータベースレベルのロールとは、データベースに対しての権限をまとめたものです。

データベースユーザーに対してデータベースのロールを設定することで、データベースに対してできる権限をひとつずつ許可していくのではなく、まとめて権限を与えることができます。

複数のデータベースロールを割り当てたり、ユーザー定義のデータベースロールを作成して割り当てることも可能です。

固定のデータベースロールには db_owner、db_securityadmin、db_accessadmin、db_backupoperator、db_ddladmin、db_datawriter、db_datareader、db_denydatawriter、db_denydatareader があります。

データベースに対して何でもできるのが db_owner、SELECT できるのが db_datareader、INSERT/UPDATE/DELETE ができるのが db_datawriter です。

[Reference] Database-Level Roles - SQL Server | Microsoft Docs

SSMS からユーザーにサーバーロールを設定する方法

SQL Server Management Studio から、ログインユーザーにサーバーロールを設定する方法をご説明します。

オブジェクトエクスプローラーの [セキュリティ] > [ログイン] で右クリックして [新しいログイン] を選択します。

新規ログイン作成の画面が表示されるので、左側で [サーバーロール] ページを選択します。

画面にサーバーロールの一覧が表示されるので、ここでログインユーザーにサーバーロールを設定できます。

既存のログインのサーバーロールを変更したい時は、ログインで右クリックしてプロパティを表示すると、同様にサーバーロールを選択するページがでてきます。

SSMS からユーザーにデータベースロールを設定する方法

SQL Server Management Studio から、データベースユーザーにデータベースロールを設定する方法をご紹介します。

新しいログインを生成時に同時に設定することができます。

オブジェクトエクスプローラーの [セキュリティ] > [ログイン] で右クリックして [新しいログイン] を選択します。

新規ログイン作成の画面が表示されるので、左側で [ユーザーマッピング] ページを選択します。

上のグリッドでユーザーをマップするデータベースを選択します。

そうすると、下にそのデータベースのデータベースロールが表示されるので、ここでデータベースユーザにデータベースロールを設定できます。

既存のデータベースユーザーのデータベースロールを変更したい時は、[データベース] > [セキュリティ] > [ユーザー]でユーザーを右クリックして [プロパティ] を選択します。

データベースユーザーのプロパティ画面が表示されるので、左側で [メンバーシップ] ページを選択します。

画面にデータベースロールの一覧が表示されるので、ここでデータベースユーザーににデータベースロールを設定できます。

GRANT EXECUTE TO [ユーザー名]

CREATE ROLE [execute用ロール名];
GRANT EXECUTE TO [execute用ロール名];

以上、固定サーバーレベルのロールと、データベースレベルのロールと設定方法についてご説明しました。