【SQL】REVOKEの使い方｜SSMSで権限を取り消す方法を初心者向けに解説
SQL Serverでユーザーに付与した権限を取り消したい場合は、REVOKEを使用します。
ただ、REVOKEはSELECT文やUPDATE文と比べると使う機会が少ないため、「どのユーザーに対して実行するのか」「GRANTやDENYと何が違うのか」で迷いやすいコマンドです。
この記事では、SQL Server LocalDBとSQL Server Management Studioを使って、サンプルデータベースの作成、ユーザー作成、GRANTによる権限付与、REVOKEによる権限取り消し、実行結果の確認までを順番に解説します。
最終的には、SSMS上でREVOKEを使ってユーザーのSELECT権限を取り消す流れを実際に確認できるようになります。
この記事でわかること
この記事では、SQL ServerでREVOKEを使うために必要な基本操作を学びます。
- REVOKEの基本的な役割
- SQL Server LocalDBへSSMSで接続する方法
- サンプルデータベースとテーブルを作成する方法
- SQL Server認証用のログインとユーザーを作成する方法
- GRANTでSELECT権限を付与する方法
- REVOKEでSELECT権限を取り消す方法
- 権限エラーが出たときの確認ポイント
動作環境
この記事では、Windows環境でSQL Server LocalDBとSSMSを使う前提で進めます。
バージョンは一例です。環境によって画面表示や細かい操作が異なる場合があります。
| 項目 | 内容 |
|---|---|
| OS | Windows 11 |
| SQL Server | SQL Server LocalDB |
| 管理ツール | SQL Server Management Studio（SSMS） |
| 接続先 | (LocalDB)\MSSQLLocalDB |
| 使用言語 | SQL |
環境構築ができていない場合は、以下を参考にしてください。
また、C#.NETで実行したい方は本記事と以下の記事を見ながら試行してみてください。
完成イメージ
この記事のゴールは、SSMS上で次の流れを確認することです。
- サンプル用のデータベースを作成する
- 社員テーブルを作成する
- 確認用のログインとユーザーを作成する
- GRANTでSELECT権限を付与する
- ユーザーを切り替えてSELECTできることを確認する
- REVOKEでSELECT権限を取り消す
- 再度SELECTを実行し、権限エラーになることを確認する
つまり、ただREVOKE文の書き方を覚えるだけではなく、権限を付与してから取り消すまでの一連の流れを実際に確認します。
今回の最終的な状態は、確認用ユーザーが社員テーブルをSELECTできない状態です。
REVOKEの実践
ここから、SSMSを使ってREVOKEを実行する手順を解説します。
作業は順番に進めることをおすすめします。
途中のSQLだけを抜き出して実行すると、データベースやユーザーが存在せず、エラーになる可能性があります。
サンプルデータベースを作成する
まずは、REVOKEの動作確認に使うサンプルデータベースを作成します。
SSMSの上部メニューから「新しいクエリ」をクリックし、以下のSQLを実行してください。
今回は、RevokeSampleDBというデータベースを作成します。
CREATE DATABASE RevokeSampleDB;正常に実行できると、メッセージ欄に「コマンドは正常に完了しました。」のような内容が表示されます。
オブジェクトエクスプローラーに表示されない場合は、データベースフォルダを右クリックして「最新の情報に更新」を実行してください。
使用するデータベースを切り替える
データベースを作成しただけでは、まだそのデータベースを使用している状態ではありません。
次のSQLを実行して、使用するデータベースをRevokeSampleDBに切り替えます。
この操作を忘れると、別のデータベースにテーブルやユーザーを作ってしまう可能性があります。
USE RevokeSampleDB;SSMSでは、クエリウィンドウ上部のデータベース選択欄からもデータベースを切り替えられます。
ただし、この記事では手順を明確にするため、SQL内でUSE文を実行します。
Employeesテーブルを作成する
次に、権限確認用のテーブルを作成します。
今回は、社員情報を管理するEmployeesテーブルを作成します。
REVOKEの確認が目的なので、テーブル構成はシンプルにしています。
CREATE TABLE Employees
(
EmployeeID INT NOT NULL PRIMARY KEY,
EmployeeName NVARCHAR(50) NOT NULL,
DepartmentName NVARCHAR(50) NOT NULL
);このSQLでは、社員ID、社員名、部署名を持つテーブルを作成しています。
REVOKEはデータを更新するSQLではなく、権限を操作するSQLです。
そのため、まずは権限を確認するための対象テーブルを用意します。
サンプルデータを登録する
テーブルを作成したら、SELECTの確認に使うデータを登録します。
以下のSQLを実行してください。
ここでは、3件のサンプルデータをEmployeesテーブルに追加します。
INSERT INTO Employees
(
EmployeeID,
EmployeeName,
DepartmentName
)
VALUES
(1, N'山田 太郎', N'営業部'),
(2, N'佐藤 花子', N'総務部'),
(3, N'鈴木 一郎', N'開発部');登録できたら、次のSQLでデータを確認します。
SELECT
EmployeeID,
EmployeeName,
DepartmentName
FROM
Employees;実行結果に3件のデータが表示されれば、確認用テーブルの準備は完了です。
確認用ログインを作成する
次に、権限を付与・取り消しするための確認用ログインを作成します。
ログインは、SQL Serverに接続するためのアカウントです。
今回は、revoke_test_loginというSQL Server認証ログインを作成します。
CREATE LOGIN revoke_test_login
WITH PASSWORD = 'TestPassword123!';このSQLでは、SQL Server認証用のログインを作成しています。
パスワードは学習用の例です。
実務では、推測されにくいパスワードを設定し、パスワード管理ルールに従ってください。
データベースユーザーを作成する
SQL Serverのログインを作成しただけでは、RevokeSampleDB内のテーブルを操作できません。
データベース内で使用するユーザーを作成する必要があります。
次のSQLで、revoke_test_userというデータベースユーザーを作成します。
CREATE USER revoke_test_user
FOR LOGIN revoke_test_login;ログインとユーザーの違いは、初心者がつまずきやすいポイントです。
簡単にいうと、ログインはSQL Serverへ入るためのアカウントで、ユーザーは特定のデータベース内で操作するためのアカウントです。
| 種類 | 役割 |
|---|---|
| ログイン | SQL Serverへ接続するためのアカウント |
| ユーザー | データベース内で操作するためのアカウント |
今回のREVOKEでは、データベース内のテーブル権限を操作するため、データベースユーザーに対して権限を付与・取り消しします。
GRANTでSELECT権限を付与する
REVOKEの動作を確認するために、まずはGRANTでSELECT権限を付与します。
以下のSQLを実行してください。
ここでは、revoke_test_userに対して、EmployeesテーブルをSELECTする権限を付与します。
GRANT SELECT
ON Employees
TO revoke_test_user;このSQLを実行すると、revoke_test_userはEmployeesテーブルのデータを参照できるようになります。
REVOKEは、ここで付与したSELECT権限を取り消すために使用します。
権限が付与されていることを確認する
次に、revoke_test_userとしてSELECTできるか確認します。
SQL Serverでは、EXECUTE AS USERを使うことで、指定したユーザーとしてSQLを実行できます。
以下のSQLを実行してください。
ここでは、revoke_test_userに切り替えてSELECTできるかを確認します。
EXECUTE AS USER = 'revoke_test_user';
SELECT
EmployeeID,
EmployeeName,
DepartmentName
FROM
Employees;
REVERT;実行結果にEmployeesテーブルのデータが表示されれば、SELECT権限が正しく付与されています。
最後のREVERTは、元の実行ユーザーに戻すためのSQLです。
REVERTを忘れると、以降のSQLがrevoke_test_userとして実行される場合があります。
REVOKEの基本構文を確認する
REVOKEの基本構文は、以下の形です。
REVOKE 権限名
ON 対象
FROM ユーザー名;今回の場合は、SELECT権限をEmployeesテーブルから取り消しています。
GRANTではTOを使いましたが、REVOKEではFROMを使う点に注意してください。
REVOKEでSELECT権限を取り消す
ここからREVOKEを実行します。
以下のSQLを実行してください。
REVOKE SELECT
ON Employees
FROM revoke_test_user;このSQLでは、revoke_test_userに付与していたEmployeesテーブルのSELECT権限を取り消します。
REVOKE後にSELECTできないことを確認する
最後に、REVOKE後の動作を確認します。
再度、revoke_test_userとしてEmployeesテーブルをSELECTしてみます。
REVOKEが成功していれば、SELECT権限がないためエラーになります。
EXECUTE AS USER = 'revoke_test_user';
SELECT
EmployeeID,
EmployeeName,
DepartmentName
FROM
Employees;
REVERT;実行すると、環境によって文言は異なりますが、以下のような権限エラーが表示される場合があります。
このエラーが表示されれば、revoke_test_userからSELECT権限が取り消されていることを確認できます。
エラーと聞くと失敗に見えるかもしれませんが、今回はREVOKEの確認なので、権限エラーになることが正しい結果です。
現在の権限を確認する
必要に応じて、現在の権限状態を確認できます。
以下のSQLを実行すると、RevokeSampleDB内の権限情報を確認できます。
ここでは、revoke_test_userに関連する権限を確認します。
SELECT
USER_NAME(database_permissions.grantee_principal_id) AS UserName,
database_permissions.permission_name AS PermissionName,
database_permissions.state_desc AS PermissionState,
OBJECT_NAME(database_permissions.major_id) AS ObjectName
FROM
sys.database_permissions AS database_permissions
WHERE
USER_NAME(database_permissions.grantee_principal_id) = 'revoke_test_user';REVOKE後は、付与していたSELECT権限の行が表示されない状態になります。
ただし、ロール経由で権限が付与されている場合などは、結果の見え方が変わる場合があります。
実務では、ユーザー単体の権限だけでなく、所属ロールも含めて確認してください。
よくあるエラー・注意点
REVOKEは権限操作のSQLなので、通常のSELECTやUPDATEよりもエラー原因がわかりにくい場合があります。
特に初心者の場合は、データベースの選択ミス、ログインとユーザーの混同、GRANTとREVOKEの書き方の違いでつまずきやすいです。
以下に、よくあるエラーと確認ポイントを整理します。
| エラー・注意点 | 原因 | 対処法 |
|---|---|---|
| Cannot find the user と表示される | 対象のデータベースにユーザーが作成されていない | CREATE USERを実行したか確認する |
| Cannot find the object と表示される | テーブル名が間違っている、またはDBが違う | USE RevokeSampleDBを実行してから確認する |
| SELECTできてしまう | ロール経由で別の権限が付与されている可能性がある | ユーザーが所属するロールの権限も確認する |
| Permission denied と表示される | REVOKEを実行する側に権限がない | 管理者権限を持つユーザーで実行する |
| REVOKEしてもDENYが残る | REVOKEとDENYの役割を混同している | DENYを解除したい場合は、対象のDENY設定を確認する |
| ログインできない | SQL Server認証が無効、またはパスワードが違う | SQL Serverの認証モードやログイン情報を確認する |
| REVERTを忘れた | EXECUTE AS USERのまま作業している | REVERTを実行して元のユーザーに戻す |
GRANT・REVOKE・DENYの違い
権限操作では、GRANT、REVOKE、DENYの違いを理解しておくことが重要です。
この3つを混同すると、REVOKEしたのにアクセスできる、またはGRANTしたのにアクセスできないという状態になります。
基本的な違いは、許可・取り消し・拒否です。
| SQL | 役割 |
|---|---|
| GRANT | 権限を付与する |
| REVOKE | 付与または拒否されている権限を取り消す |
| DENY | 権限を明示的に拒否する |
GRANTは許可、REVOKEは取り消し、DENYは拒否です。
特にDENYは明示的な拒否なので、通常の権限確認では強く影響します。
初心者のうちは、まずGRANTとREVOKEの基本を押さえてから、DENYを学ぶと理解しやすいです。
REVOKE ALLは基本的に使わない
SQL ServerにはREVOKE ALLという書き方もあります。
ただし、現在のSQL ServerではREVOKE ALLは非推奨とされています。
そのため、新しくSQLを書く場合は、取り消したい権限名を明示する書き方を使うのが安全です。
今回の記事でも、以下のようにSELECT権限を指定して取り消しています。
REVOKE SELECT
ON Employees
FROM revoke_test_user;権限名を明示すると、何の権限を取り消しているのかが読みやすくなります。
保守性の面でも、REVOKE ALLより明示的な書き方のほうが扱いやすいです。
本番環境で実行する前に確認すること
REVOKEはユーザーの権限を変更するSQLです。
本番環境で実行すると、アプリケーションや業務ユーザーが突然データを参照できなくなる可能性があります。
そのため、本番環境で実行する前に、対象ユーザー・対象テーブル・取り消す権限を必ず確認してください。
最低限、以下の内容は確認しておくことをおすすめします。
- どのデータベースで実行するのか
- どのユーザーの権限を取り消すのか
- どのテーブルやスキーマが対象なのか
- SELECT、INSERT、UPDATE、DELETEのどれを取り消すのか
- アプリケーションや業務に影響がないか
- ロール経由の権限が残っていないか
まとめ
この記事では、SQL Server LocalDBとSSMSを使って、REVOKEでユーザー権限を取り消す方法を解説しました。
今回行った内容は、以下の通りです。
- SSMSでSQL Server LocalDBに接続した
- サンプルデータベースとEmployeesテーブルを作成した
- 確認用ログインとデータベースユーザーを作成した
- GRANTでSELECT権限を付与した
- EXECUTE AS USERで別ユーザーとしてSELECTを確認した
- REVOKEでSELECT権限を取り消した
- REVOKE後にSELECTできないことを確認した
REVOKEは、ユーザーやロールの権限管理で使う重要なSQLです。
単体で文法だけを覚えるよりも、GRANTで権限を付与し、REVOKEで取り消し、実際にエラーになるところまで確認すると理解しやすくなります。
次に学ぶなら、GRANT、DENY、ロール管理、スキーマ単位の権限付与を学ぶと、SQL Serverの権限管理をより実務に近い形で理解できます。
