【SQL】GRANTの使い方｜SSMSでユーザーに権限を付与する方法を初心者向けに解説
SQL Serverを複数人で使用するときは、すべてのユーザーに同じ操作を許可するのではなく、担当者ごとに必要な権限だけを設定することが重要です。
例えば、データを確認する担当者にはSELECTのみを許可し、登録担当者にはINSERTも許可するといった制御ができます。
このような権限設定に使用するのが、SQLのGRANTです。
この記事では、SQL Server Management Studioを使用して、検証用ユーザーの作成からGRANTによる権限付与、権限が正しく設定されたか確認するところまで解説します。
この記事でわかること
この記事では、SSMSを使ってSQL Serverの権限管理を実際に試す方法を学びます。
- SQL Serverで使用するGRANTの基本
- ログインを持たない検証用ユーザーを作成する方法
- テーブルにSELECT権限を付与する方法
- 複数の権限をまとめて付与する方法
- EXECUTE AS USERを使って別ユーザーの権限を確認する方法
- GRANT実行前後の動作を比較する方法
- 権限設定で発生しやすいエラーと対処法
動作環境
この記事では、Windows環境でSQL Server LocalDBとSSMSを使う前提で進めます。
バージョンは一例です。環境によって画面表示や細かい操作が異なる場合があります。
| 項目 | 内容 |
|---|---|
| OS | Windows 11 |
| SQL Server | SQL Server LocalDB |
| 管理ツール | SQL Server Management Studio（SSMS） |
| 接続先 | (LocalDB)\MSSQLLocalDB |
| 使用言語 | SQL |
環境構築ができていない場合は、以下を参考にしてください。
また、C#.NETで実行したい方は本記事と以下の記事を見ながら試行してみてください。
完成イメージ
この記事では、商品情報を保存するProductsテーブルと、権限確認用のGrantTestUserユーザーを作成します。
最初はGrantTestUserに権限を与えず、SELECT文を実行するとエラーになることを確認します。
その後、GRANTでSELECT権限だけを付与し、データを取得できるようになったことを確認します。
さらに、INSERT権限がない状態ではデータを追加できないことを確認したあと、INSERT権限を追加し、登録できる状態に変更します。
最終的には、次のような権限になります。
| 操作 | 権限付与前 | SELECT付与後 | INSERT付与後 |
|---|---|---|---|
| データの取得 | エラー | 実行可能 | 実行可能 |
| データの追加 | エラー | エラー | 実行可能 |
| データの更新 | エラー | エラー | エラー |
| データの削除 | エラー | エラー | エラー |
このように、ユーザーに対して必要な操作だけを許可できる状態が完成イメージです。
GRANTの実践
ここからは、SSMSを使ってGRANTの動作を順番に確認します。
既存のデータベースに影響を与えないように、この記事では練習用のGrantSampleDBデータベースを新しく作成します。
学習用データベースを作成する
次に、GRANTを確認するための学習用データベースを作成します。
SSMSで新しいクエリを開き、以下のSQLを実行します。
CREATE DATABASE GrantSampleDB;実行後、左側のデータベース一覧を更新すると、GrantSampleDBが表示されます。
SQLを入力したら、上部の「実行」をクリックするか、F5キーを押します。
このSQLでは、GrantSampleDBが存在しない場合だけ、新しいデータベースを作成しています。
すでに同じ名前のデータベースが存在する場合は、重複作成を行いません。
作成後、オブジェクトエクスプローラーの「データベース」を右クリックし、「最新の情報に更新」を選択してください。
GrantSampleDBが表示されれば、データベースの作成は完了です。
作成したデータベースを使うために、以下のSQLも実行します。
USE GrantSampleDB;これで、以降のSQLはGrantSampleDBに対して実行されます。
Productsテーブルを作成する
続いて、権限確認に使用するProductsテーブルを作成します。
以下のSQLを実行してください。
IF OBJECT_ID(N'dbo.Products', N'U') IS NULL
BEGIN
CREATE TABLE dbo.Products
(
ProductId INT IDENTITY(1, 1) NOT NULL,
ProductName NVARCHAR(100) NOT NULL,
Price INT NOT NULL,
CONSTRAINT PK_Products PRIMARY KEY (ProductId)
);
END;このテーブルには、商品ID、商品名、価格を保存します。
| 列名 | データ型 | 内容 |
|---|---|---|
| ProductId | INT | 商品を識別する番号 |
| ProductName | NVARCHAR(100) | 商品名 |
| Price | INT | 商品価格 |
テーブル名の前に指定しているdboは、SQL Serverで一般的に使用されるスキーマ名です。
GRANTで対象を指定するときも、dbo.Productsのようにスキーマ名を含めると、対象が明確になります。
確認用データを登録する
Productsテーブルに、動作確認用の商品データを登録します。
以下のSQLを実行してください。
IF NOT EXISTS
(
SELECT 1
FROM dbo.Products
)
BEGIN
INSERT INTO dbo.Products
(
ProductName,
Price
)
VALUES
(N'ノートパソコン', 120000),
(N'キーボード', 8000),
(N'マウス', 4000);
END;登録したデータを確認するため、次のSELECT文を実行します。
SELECT
ProductId,
ProductName,
Price
FROM dbo.Products;次のようなデータが表示されれば、テーブルの準備は完了です。
ProductIdの値は、実行状況によって異なる場合があります。
権限確認用のユーザーを作成する
GRANTは、ユーザーやロールなどのデータベースプリンシパルに対して権限を付与します。
この記事では、SSMS上で権限を確認するために、ログインを持たないデータベースユーザーを作成します。
以下のSQLを実行してください。
IF DATABASE_PRINCIPAL_ID(N'GrantTestUser') IS NULL
BEGIN
CREATE USER GrantTestUser WITHOUT LOGIN;
END;WITHOUT LOGINを指定したユーザーは、SQL Serverへ直接ログインできません。
ただし、権限を付与したり、EXECUTE AS USERを使ってそのユーザーとして処理を実行したりできます。
今回のように、SSMS上で権限だけを検証したい場合に利用できます。
GRANTの基本構文を確認する
SQL Serverでテーブルに権限を付与するときは、次の構文を使用します。
GRANT 権限名
ON OBJECT::スキーマ名.オブジェクト名
TO ユーザー名;それぞれの指定内容は次のとおりです。
| 指定項目 | 内容 | 例 |
|---|---|---|
| GRANT | 権限を付与する命令 | GRANT |
| 権限名 | 許可する操作 | SELECT |
| ON | 権限を設定する対象 | OBJECT::dbo.Products |
| TO | 権限を与える相手 | GrantTestUser |
テーブルでよく使用する権限には、次のものがあります。
| 権限 | 許可される操作 |
|---|---|
| SELECT | データの取得 |
| INSERT | データの追加 |
| UPDATE | データの更新 |
| DELETE | データの削除 |
| REFERENCES | 外部キーなどからの参照 |
例えば、ProductsテーブルのSELECT権限を付与する場合は、次のように記述します。
GRANT SELECT
ON OBJECT::dbo.Products
TO GrantTestUser;OBJECT::は省略できる場合がありますが、この記事では権限の対象がオブジェクトであることを明確にするために記述します。
権限付与前にSELECT文を実行する
GRANTを実行する前に、GrantTestUserがProductsテーブルを参照できないことを確認します。
以下のSQLでは、EXECUTE AS USERを使用して、一時的にGrantTestUserとして処理を実行します。
EXECUTE AS USER = 'GrantTestUser';
BEGIN TRY
SELECT
ProductId,
ProductName,
Price
FROM dbo.Products;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
REVERT;権限を付与していないため、SELECT権限が拒否されたことを示すメッセージが表示されます。
環境によって表示内容は異なる場合がありますが、一般的には次のような内容です。
オブジェクト 'Products'、データベース 'GrantSampleDB'、スキーマ 'dbo' に対する SELECT 権限が拒否されました。最後のREVERTは、EXECUTE AS USERで切り替えた実行ユーザーを元に戻す命令です。
REVERTを実行し忘れると、その後のSQLもGrantTestUserの権限で実行される可能性があるため注意してください。
SELECT権限を付与する
GrantTestUserに、ProductsテーブルのSELECT権限を付与します。
以下のGRANTを実行してください。
GRANT SELECT
ON OBJECT::dbo.Products
TO GrantTestUser;「コマンドは正常に完了しました」と表示されれば、権限付与は完了です。
このSQLでは、GrantTestUserに対して、dbo.Productsのデータを取得する権限だけを付与しています。
INSERT、UPDATE、DELETEの権限は付与していないため、データの変更操作はまだ実行できません。
SELECT権限を確認する
SELECT権限の付与後に、もう一度GrantTestUserとしてSELECT文を実行します。
以下のSQLを実行してください。
EXECUTE AS USER = 'GrantTestUser';
SELECT
ProductId,
ProductName,
Price
FROM dbo.Products;
REVERT;Productsテーブルの商品データが表示されれば、SELECT権限が正しく付与されています。
実行結果には、ノートパソコン、キーボード、マウスなどのデータが表示されます。
この時点でGrantTestUserができるのは、Productsテーブルのデータを取得することだけです。
INSERT権限がないことを確認する
次に、GrantTestUserでデータの追加を試します。
SELECT権限しか付与していないため、INSERT文はエラーになることが正常です。
以下のSQLを実行してください。
EXECUTE AS USER = 'GrantTestUser';
BEGIN TRY
INSERT INTO dbo.Products
(
ProductName,
Price
)
VALUES
(
N'モニター',
30000
);
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
REVERT;INSERT権限が拒否されたことを示すエラーが表示されます。
SELECT権限を付与しても、INSERT権限まで自動的に付与されるわけではありません。
このように、GRANTでは操作ごとに権限を分けて管理できます。
INSERT権限を追加する
GrantTestUserに、ProductsテーブルへのINSERT権限を追加します。
以下のSQLを実行してください。
GRANT INSERT
ON OBJECT::dbo.Products
TO GrantTestUser;これでGrantTestUserには、Productsテーブルに対するSELECT権限とINSERT権限が付与されました。
すでに付与されているSELECT権限は削除されません。
GRANTを追加で実行すると、既存の権限を残したまま新しい権限を追加できます。
INSERT権限を確認する
INSERT権限の付与後に、もう一度データを登録します。
以下のSQLを実行してください。
EXECUTE AS USER = 'GrantTestUser';
INSERT INTO dbo.Products
(
ProductName,
Price
)
VALUES
(
N'モニター',
30000
);
REVERT;エラーが表示されず、「1行処理されました」と表示されれば登録成功です。
登録結果を確認するため、次のSELECT文を実行します。
SELECT
ProductId,
ProductName,
Price
FROM dbo.Products
ORDER BY ProductId;モニターのデータが追加されていれば、INSERT権限が正しく付与されています。
複数の権限をまとめて付与する
GRANTでは、複数の権限をカンマ区切りで指定できます。
例えば、SELECT、INSERT、UPDATEの3つをまとめて付与する場合は、次のように記述します。
GRANT SELECT, INSERT, UPDATE
ON OBJECT::dbo.Products
TO GrantTestUser;複数の操作を許可するユーザーを作成する場合は、この書き方を使用するとSQLを簡潔にできます。
ただし、必要のない権限までまとめて付与するのは避けてください。
必要最小限の権限だけを設定する考え方は、最小権限の原則と呼ばれます。
付与されている権限を確認する
GrantTestUserに付与されている権限は、システムビューから確認できます。
以下のSQLを実行してください。
SELECT
USER_NAME(database_permissions.grantee_principal_id) AS UserName,
OBJECT_SCHEMA_NAME(database_permissions.major_id) AS SchemaName,
OBJECT_NAME(database_permissions.major_id) AS ObjectName,
database_permissions.permission_name AS PermissionName,
database_permissions.state_desc AS PermissionState
FROM sys.database_permissions AS database_permissions
WHERE database_permissions.grantee_principal_id =
DATABASE_PRINCIPAL_ID(N'GrantTestUser')
ORDER BY
ObjectName,
PermissionName;GrantTestUserにSELECTやINSERTを付与している場合、実行結果にそれぞれの権限が表示されます。
| 項目 | 内容 |
|---|---|
| UserName | 権限を持つユーザー |
| SchemaName | 対象のスキーマ |
| ObjectName | 対象のテーブルなど |
| PermissionName | SELECTやINSERTなどの権限 |
| PermissionState | GRANTやDENYなどの状態 |
この確認方法を覚えておくと誰にどの権限が付与されているかをSQLで調査できます。
検証環境を元に戻す
練習後に作成したデータベースを削除する場合は、次のSQLを実行します。
この操作を行うと、GrantSampleDB内のテーブルやデータ、ユーザーもすべて削除されます。
必要なデータが残っていないことを確認してから実行してください。
USE master;
IF DB_ID(N'GrantSampleDB') IS NOT NULL
BEGIN
ALTER DATABASE GrantSampleDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
DROP DATABASE GrantSampleDB;
END;削除せずに今後も練習で使用する場合は、この手順を実行する必要はありません。
実務環境では、データベースを削除する前にバックアップや影響範囲の確認が必要です。
よくあるエラー・注意点
GRANTでは、対象のデータベース、ユーザー、テーブルを正しく指定する必要があります。
特に初心者のうちは、別のデータベースを選択したままSQLを実行するミスが起こりやすいため注意してください。
| エラー・症状 | 主な原因 | 対処法 |
|---|---|---|
| ユーザーが存在しないと表示される | GrantTestUserを作成していない、または別のデータベースで実行している | USE GrantSampleDBを実行し、CREATE USERが完了しているか確認する |
| Productsが見つからない | テーブル名やスキーマ名が間違っている | dbo.Productsのようにスキーマ名を含めて指定する |
| SELECT権限が拒否される | SELECT権限が付与されていない | GRANT SELECTを実行する |
| INSERT権限が拒否される | SELECTだけを付与している | 必要な場合のみGRANT INSERTを実行する |
| GRANTを実行できない | 実行ユーザーに権限付与を行う権限がない | データベース所有者など、適切な権限を持つユーザーで実行する |
| REVERT後も処理がおかしい | EXECUTE AS USERとREVERTの実行範囲がずれている | EXECUTE AS USERからREVERTまでをまとめて実行する |
| CREATE DATABASEでエラーになる | 同名のデータベースが存在する | オブジェクトエクスプローラーまたはDB_IDで存在を確認する |
| データが重複して登録される | INSERT文を複数回実行している | 不要なデータをDELETEするか、データベースを作り直す |
| LocalDBに別端末から接続できない | LocalDBは主にローカル開発向けである | 外部接続が必要な場合は通常のSQL Server環境を検討する |
GRANTを実行するデータベースに注意する
GRANTは、現在選択されているデータベース内のユーザーとオブジェクトに対して実行されます。
SSMSのデータベース選択欄だけに頼らず、SQLの先頭にUSE文を記述しておくと安全です。
USE GrantSampleDB;異なるデータベースで実行すると、ユーザーやテーブルが見つからないエラーが発生します。
スキーマ名を省略しない
テーブル名をProductsだけで指定しても動作する場合がありますが、実務ではdbo.Productsのようにスキーマ名まで指定することをおすすめします。
GRANT SELECT
ON OBJECT::dbo.Products
TO GrantTestUser;同じ名前のテーブルが別のスキーマに存在する場合でも、対象を明確に指定できます。
必要以上の権限を付与しない
SELECTだけでよいユーザーに、INSERT、UPDATE、DELETEまで付与すると、誤ってデータを変更される可能性があります。
権限を設定するときは、そのユーザーが本当に必要とする操作を先に整理してください。
特に、CONTROLやALTERなどの強い権限は、影響範囲を理解せずに付与しないようにします。
WITH GRANT OPTIONは安易に使用しない
GRANTには、権限を受け取ったユーザーが、さらに別のユーザーへ同じ権限を付与できるようにするWITH GRANT OPTIONがあります。
GRANT SELECT
ON OBJECT::dbo.Products
TO GrantTestUser
WITH GRANT OPTION;WITH GRANT OPTIONを付けると権限管理が複雑になりやすいため、管理上の明確な理由がある場合だけ使用してください。
通常の業務ユーザーへ安易に付与することはおすすめしません。
EXECUTE AS USERの後はREVERTを実行する
EXECUTE AS USERを使用すると、現在のセッション内で実行ユーザーが切り替わります。確認後は、必ず次のSQLを実行して元のユーザーへ戻します。
REVERT;権限確認用のSQLとREVERTをセットで実行すると、戻し忘れを防ぎやすくなります。
まとめ
この記事では、SQL Server Management Studioを使って、GRANTでユーザーに権限を付与する方法を解説しました。
SSMSだけでもEXECUTE AS USERを使用することで、権限付与前後の動作を比較できます。
重要なポイントは次のとおりです。
- GRANTはユーザーやロールに操作権限を付与する
- テーブルへの権限はSELECT、INSERT、UPDATE、DELETEなどに分かれている
- LocalDBでの検証にはログインを持たないユーザーを利用できる
- EXECUTE AS USERで別ユーザーの権限を確認できる
- 確認後はREVERTで元の実行ユーザーへ戻す
- テーブルはdbo.Productsのようにスキーマ名を含めて指定する
- 必要最小限の権限だけを付与する
GRANTを理解したあとは、権限を取り消すREVOKE文と、権限を明示的に拒否するDENY文を学ぶと、SQL Serverの権限管理をより深く理解できます。
