あの面倒な権限付与もラクラク！SQL Server 2000でシステムビューを活用したストアドプロシージャアクセス許可設定

2024-06-09

SQL Server 2000 でデータベース内のすべてのストアドプロシージャに対してユーザーに実行権限を付与する方法

このチュートリアルでは、SQL Server 2000 データベース内のすべてのストアドプロシージャに対してユーザーに実行権限を付与する方法について説明します。

要件

SQL Server 2000 がインストールおよび構成されていること
データベースへのアクセス権を持つユーザーアカウント

方法

以下の 2 つの方法で、データベース内のすべてのストアドプロシージャに対してユーザーに実行権限を付与することができます。

方法 1: SQL Server Management Studio を使用する

SQL Server Management Studio を開き、データベースに接続します。
オブジェクトエクスプローラーで、権限を付与するデータベースを展開します。
プログラミング フォルダを展開します。
ストアドプロシージャ フォルダを展開します。
権限を付与するすべてのストアドプロシージャを選択します。
右クリックして プロパティ を選択します。
ストアドプロシージャのプロパティ ダイアログボックスで、権限ページを選択します。
ユーザー または ロール ボックスで、権限を付与するユーザーまたはロールを選択します。
明示的な権限 グリッドで、 EXECUTE 権限を選択します。
OK をクリックします。

方法 2: T-SQL を使用する

標準バーの 新しいクエリ ボタンをクリックします。
以下の T-SQL スクリプトをクエリエディタに貼り付けます。

GRANT EXECUTE ON SCHEMA::PROCEDURE TO [ユーザー名];

[ユーザー名] を、権限を付与するユーザーの名前に置き換えます。

追加のヒント

特定のスキーマ内のすべてのストアドプロシージャに対してユーザーに実行権限を付与するには、次のスクリプトを使用します。

GRANT EXECUTE ON SCHEMA::PROCEDURE TO [ユーザー名];

GRANT EXECUTE ON SCHEMA::PROCEDURE TO PUBLIC;

注意事項

ユーザーに EXECUTE 権限を付与すると、そのユーザーはストアドプロシージャを実行できますが、ストアドプロシージャを変更することはできません。
ストアドプロシージャを変更する権限をユーザーに付与するには、ALTER 権限も付与する必要があります。

サンプルコード：SQL Server 2000 でデータベース内のすべてのストアドプロシージャに対してユーザーに実行権限を付与する

-- 特定のユーザーにすべてのストアド プロシージャへの実行権限を付与する

GRANT EXECUTE ON SCHEMA::PROCEDURE TO [ユーザー名];

-- データベース内のすべてのストアド プロシージャに対してすべてのユーザーに実行権限を付与する

GRANT EXECUTE ON SCHEMA::PROCEDURE TO PUBLIC;

説明

GRANT EXECUTE：この句は、EXECUTE 権限を付与することを指定します。
ON SCHEMA::PROCEDURE：この句は、権限を付与するオブジェクトの種類とスキーマを指定します。この場合、SCHEMA は既定のスキーマを指し、PROCEDURE はストアドプロシージャを指します。
TO [ユーザー名]：この句は、権限を付与するユーザーを指定します。

例

以下の例は、myuser というユーザーにデータベース mydb 内のすべてのストアドプロシージャに対して実行権限を付与します。

GRANT EXECUTE ON SCHEMA::PROCEDURE TO myuser;

GRANT EXECUTE ON SCHEMA::PROCEDURE TO PUBLIC;

上記のスクリプトを実行する前に、SQL Server Management Studio でデータベースに接続していることを確認してください。

SQL Server 2000 でデータベース内のすべてのストアドプロシージャに対してユーザーに実行権限を付与するその他の方法

方法 3: システムビューを使用する

DECLARE @sql NVARCHAR(4000);

SELECT @sql = N'GRANT EXECUTE ON ' + OBJECT_SCHEMA(o.object_id) + '.' +
                  OBJECT_NAME(o.object_id) + ' TO ' + N'[' + @user + ']';

FROM sys.procedures o
WHERE o.is_ms_shipped = 0;

EXEC sp_executesql @sql;

方法 4: PowerShell を使用する

PowerShell を開きます。
以下の PowerShell コマンドレットを実行します。

$user = "ユーザー名"
$database = "データベース名"

$procedures = Get-SqlDatabaseStoredProcedure -DatabaseName $database

foreach ($procedure in $procedures) {
    GRANT-SqlDatabaseStoredProcedurePermission -DatabaseName $database -StoredProcedureName $procedure.ProcedureName -User $user -PermissionType Execute
}

$database 変数に、データベースの名前を置き換えます。
Enter キーを押します。

方法 5: SSIS パッケージを使用する

SQL Server Integration Services (SSIS) を使用して、パッケージを作成します。
パッケージで、以下のタスクを実行します。
- ForEach Loop コンテナー: このコンテナーは、データベース内のすべてのストアドプロシージャを反復します。
- Execute SQL Task: このタスクは、GRANT EXECUTE ステートメントを実行して、現在のストアドプロシージャに対してユーザーに実行権限を付与します。
パッケージを実行します。