MSSQLSERVER2014 でストアドの実行権限のみ付与したい。

Question

MSSQLSERVER　に、ログインユーザー太郎を作成して
ストアドZZZ　に実行権限を渡したいので、
ストアドZZZを右クリック⇒プロパティ⇒権限⇒ユーザー太郎を選択⇒実行の許可にチェック(権限の許可者dbo)
として、ストアドを実行します。

ストアドの内容がシンプルなSELECT文だと問題ないのですが
(例：こういうやつ)

ストアドZZZ
AS

select *
from tableBBB
where 1=1

ストアドの中に動的SQLを書いてEXECで実行するとエラーになります。
(例：こういうやつ)

ストアドZZZ
AS

declare @sql nvarchar(max) = '';
set @sql += ' select *';
set @sql += ' from tableBBB';
IF 条件 BEGIN
  set @sql += ' where 1=1';
END ELSE BEGIN
　set @sql += ' where 2=2';
END

EXEC(@sql);

エラー：
SELECT 権限がオブジェクト 'tableBBB'、データベース 'DB名'、スキーマ 'dbo' で拒否されました。

これって、tableBBBテーブルの参照権限にユーザー太郎を許可するしかないのでしょうか？
できれば、ストアド以外の権限は付けたくないのです。

何卒よろしくお願いいたします。

Comments

[退会済みユーザー]

質問者さん、その後無言ですが、どうして EXECUTE を使うと対象テーブルに SELECT 権限を与える必要があるのかは Microsoft のドキュメントで説明しました。このスレッドはクローズしてください。まだ質問があるならそれを書いてください。オープンしておきたい理由があればその旨書いてください。とにかく無言で放置は NG です

Answer 1

検証したわけではないのでハズレかもしれませんが・・・

SSMS を操作して「ログインユーザー太郎」に当該ストアドプロシージャに対する「実行」権限を与えたらどうなりますか？

ハズレだったらすみません。

【追記】

下のコメント欄の 2021/10/06 12:58 の私のコメントで「自分の環境でやってみましたが、ストアドの実行権限を与えるだけではダメで、当該テーブルの SELECT 権限も必要という結果でした。詳しくは後で回答欄に追記しておきます」と書いた件です。

Windows 10 Pro 64-bit にインストールした SQL Server 2012 の以下の画像の Student テーブルで試してみました。

権限を与えるのは IIS のワーカープロセスのアカウントにした NETWORK SERVICE にしましたので、Student テーブルのあるデータベース TestDatabase のログインに NETWORK SERVICE を追加。

ストアドプロシージャを作成。

NETWORK SERVICE にストアドプロシージャの実行権限を付与。これだけでは「SELECT 権限がオブジェクト・・・」というエラーになります。

エラーメッセージは SELECT 権限がないと言っているので、NETWORK SERVICE に Student テーブルに対する SELECT 権限を与えます。

以上でエラーは解消します。実行結果は以下の通りです。

お試しください。

【追記２】

下のコメント欄の 2021/10/08 06:54 の私のコメントで「文書見つかりました。後で解答欄に書いておきます」と書いた件です。

どうして EXECUTE を使うと対象テーブルにSELECT権限を与える必要があるのかですが、そういう仕様だということのようです。以下の記事を見てください。

アクセス許可
https://docs.microsoft.com/ja-jp/sql/t-sql/language-elements/execute-transact-sql?view=sql-server-ver15#permissions

抜粋: "EXECUTE ステートメントの実行に権限は必要ありませんが、 EXECUTE 文字列内で参照されるセキュリティ保護可能なリソースに対しては権限が必要です。 たとえば、この文字列に INSERT ステートメントが含まれている場合、EXECUTE ステートメントの呼び出し元は対象のテーブルに対する INSERT 権限が必要です。"

仕様ということらしいので、ストアドに EXECUTE を使う今回の質問の例の場合、ユーザーには対象テーブルに対する SELECT 権限も与える以外に解決策はなさそうです。

（上の「EXECUTE ステートメントの実行に権限は必要ありませんが」というのはストアドの「実行」権限の話ではありませんので注意してください。ストアドの「実行」権限はストアド内に EXECUTE ステートメントを使う/使わないにかかわらず、ユーザーに与える必要があります）

Comments

[Com]

ご回答ありがとうございます。
はい。まさしくその作業が冒頭に書いている
ストアドZZZを右クリック⇒プロパティ⇒権限⇒ユーザー太郎を選択⇒実行の許可にチェック(権限の許可者dbo)
の内容になります。

キャプチャありがとうございます！

[退会済みユーザー]

> はい。まさしくその作業が冒頭に書いているストアドZZZを右クリック⇒プロパティ⇒権限⇒ユーザー太郎を選択⇒実行の許可にチェック(権限の許可者dbo)の内容になります。

そうだったのですか。余計な回答内容だったようですみません。

[Com]

ありがとうございます。
また、よろしくお願いいたします。

[退会済みユーザー]

自分の環境でやってみましたが、ストアドの実行権限を与えるだけではダメで、当該テーブルの SELECT 権限も必要という結果でした。詳しくは後で回答欄に追記しておきます。

[Com]

なんと。。。
ご確認いただきまして誠にありがとうございます！

[Com]

ありがとうございます！
すごく丁寧にご解説いただきまして感謝です。

やはり対象テーブルにSELECT権限を与える必要があるのですね。。。(泣)

[退会済みユーザー]

> やはり対象テーブルにSELECT権限を与える必要があるのですね。。。(泣)

自分がいろいろ試した限りではそれ以外に解決できる方法は無かったです。どうして EXECUTE を使うとダメなのか、公式文書は見つけられていませんが・・・

[退会済みユーザー]

文書見つかりました。後で解答欄に書いておきます。

[Com]

その分析力に感嘆いたします。