Windowsグループ内の、特定ユーザーのみ特定テーブルの更新権限を与えたい

Question

Windowsグループgroup1に属する、Aというユーザーのみ、テーブルtable1に対して更新権限を付けようと考えています。

ユーザーA以外のユーザーは、Windowsグループgroup1でWindows認証にてログインしています。
Aはgroup1に属していますが、SQL ServerにてA個別のログインをWindows認証にて作成している、という状態です。

group1にはtable1に対して、更新、削除に「拒否」のみを与え、Aには「許可」を与えています。
しかし、Aはtable1に対し更新することができませんでした。
調べたところ、拒否の権限の方が優先される、だそうです。

どのような設定であれば「Aのみ更新可能」とできるでしょうか。

ちなみに、A以外のユーザーに「拒否」を与えるという方法で実現できることは理解していますが、
運用上現実的ではないので、特定のユーザーのみ「許可」を与える方法を知りたいです。
また、Windows認証ではなくSQLserver認証のユーザーを用意する方法も理解はしていますが、
ポリシー上それは許可できないと上から言われてしまいました…。

環境
Microsoft SQL Server Standard:11.0.5058.0

Answer 1

権限は「許可」「拒否」「取り消し」の 3 状態があります。
「取り消し」というのは、「許可」「拒否」のいずれも設定されていない状態です。

権限で「許可」が付与されている場合はその操作ができ、それ以外では操作が出来ません。

権限の優先順位は「拒否」「許可」「取り消し」の順です。

以上から、ある権限の設定を行うと、以下の様な動作になります。

• 何も付与しない → 「取り消し」状態のため、権限なし
• 「許可」のみ付与 → 「許可」状態のため、権限あり
• 「拒否」のみ付与 → 「拒否」状態のため、権限なし
• 「許可」「拒否」を付与 → 「拒否」状態のため、権限なし

今回はグループに対して権限なし、所属ユーザーに権限あり、としたいということとお見受けしますので、以下の設定で期待する状態となるはずです。

• グループに何も付与しない → 「取り消し」状態のため、権限なし
• ユーザー A に「許可」のみ付与 → 「許可」状態のため、権限あり

一度テスト用のテーブルなど作成されて挙動を確認されるとよろしいかと思います。

---

MCITP（Database Developer/Database Administrator）

Answer 2

nagino - 引退エンジニアさん

publicロールにdb_datawriter権限を付与していたため、取り消し状態でも更新可能な状態でした。
そのため、取り消し状態＝更新可能と間違った理解をしていたようでした。

db_datawriter権限を外し、ご提案頂いたように権限を付与したところ、想定通りに動かすことができました。
ありがとうございました。