SQL Server

SQL Serverの任意のデータベースに定義されている全てのストアドプロシージャ、ユーザー定義関数の実行権限(Execute権限)を指定したユーザーに付与するSQL文について覚書。

SQL Serverでストアドプロシージャやユーザー定義関数の実行権限(Execute)をユーザーに割り当てる場合に、1つ1つ指定して権限を付与するのはめんどくさい。

何かいい方法がないかと探していたところMSDNフォーラムに記載されていた方法が今のところ一番簡単だと思ったので覚書しておく。
スポンサードリンク

指定したユーザーにまとめて実行(Execute)権限を与えるSQLの作成方法概要

簡単に説明すると、指定したデータベースに定義されているストアドプロシージャ、ユーザー定義関数の一覧を取得して、Execute権限を付与するためのSQL文を作成する。

作成したSQL文を実行してExecute権限を付与するという感じ。

データベースに定義されているストアドプロシージャとユーザー定義関数の抽出

まず最初にデータベースに定義されているストアドプロシージャとユーザー定義関数を全て抽出するSQLを実行する。

抽出方法は以下のとおり。

Microsoft SQL Server Management Studio Express で[新しいクエリ]ボタンをクリックしてクエリーウィンドウを出す。

以下のSQL文を記述し実行する。

USE test_db
GO
select 'GRANT EXECUTE ON OBJECT::' + SCHEMA_NAME(schema_id) + '.' + name + ' TO my_user'
FROM sys.objects
WHERE type = 'P'
OR type = 'FN'
OR type = 'IF'
OR type = 'TF'
GO

上記SQL文の"test_db"は、対象のストアドプロシージャ、ユーザー定義関数が存在するデータベース、"my_user"は、権限を与えるユーザー名にそれぞれ置き換えること。

実行すると以下のような結果がデータシートに表示される。
GRANT EXECUTE ON OBJECT::dbo.test_func TO my_user
当然ストアドプロシージャ、ユーザー定義関数が複数ある場合は、複数行になる。

抽出されたストアドプロシージャとユーザー定義関数に実行(Execute)権限を付与するSQLの作成方法

抽出された(データシートに表示された)結果を全選択して新たなクエリーウィンドウに貼り付ける。

貼り付けたら先頭行に以下の1行を追加する。

USE test_db

"test_db"は、対象のストアドプロシージャ、ユーザー定義関数が存在するデータベース。

つまり、以下のようなSQL文になる。

USE test_db
GRANT EXECUTE ON OBJECT::dbo.test_func TO my_user

これを実行すればExecute権限が付与される。

この例ではユーザー定義関数が1つしかないテスト用のDBを元に説明しているのであまり意味がないが、ストアドプロシージャ、ユーザー定義関数複数定義されている場合はとても楽に権限の付与ができる。

参考資料

この記事を作成するにあたって参考にしたMSDNの記事のリンクを以下に記載しておく。

MSDN SQL Serverフォーラム

スポンサードリンク
  
関連記事

 カテゴリ
SQL Server
 タグ
SQLServer データベース ストアドプロシージャ ユーザー定義関数 一括 Execute権限 
Genre:コンピュータ  Thread:データベース