Sql Sql Server T Sql Permissions

SQL Serverにおけるすべてのユーザーテーブルへの権限付与のための究極のガイド

SQL Serverにおける権限の管理は、データベースの整合性とセキュリティを維持するために重要です。一般的なシナリオの一つは、特定のデータベースロールに対してすべてのユーザーテーブルにシームレスに権限を付与する必要があるときです。新しいテーブルを追加するたびに複数のスクリプトを書くことや手動で権限を調整することで嫌気がさしているなら、あなたは一人ではありません。このガイドでは、すべてのユーザーテーブルに対してデータベースロールにSELECT、REFERENCES、INSERT、UPDATE、DELETEの権限を簡単に付与するための多用途なSQLスクリプトを作成する方法を探ります。

課題：効率的な権限付与

SQL Server Management Studio（SSMS）は通常、個々のデータベースオブジェクトのスクリプトを生成しますが、複数のテーブルを扱うときには退屈な作業になります。また、新たに追加したテーブルの権限を変更することは迅速に時間がかかるタスクとなる可能性があります。必要なのは、すべてのユーザーテーブルにわたる権限を適用するために、毎回スクリプトを見直す必要のない強力な「ファイア・アンド・フォゲット」スクリプトです。

解決策：権限付与のためのストアドプロシージャ

権限を付与するプロセスを効果的に自動化するために、すべてのユーザオブジェクトをループし、所望の権限を付与するカーソルを用いるストアドプロシージャを作成できます。プロセスを管理しやすいステップに分解してみましょう：

ステップ1：ストアドプロシージャを作成する

ストアドプロシージャを定義します。以下のSQLコードをテンプレートとして使用してください：

IF EXISTS (
    SELECT 1 FROM sysobjects
    WHERE name = 'sp_grantastic'
    AND type = 'P'
)
DROP PROCEDURE sp_grantastic
GO

CREATE PROCEDURE sp_grantastic
AS
DECLARE
 @object_name VARCHAR(30),
 @time VARCHAR(8),
 @rights VARCHAR(20),
 @role VARCHAR(20)

DECLARE c_objects CURSOR FOR
    SELECT name
    FROM sysobjects
    WHERE type IN ('P', 'U', 'V')
    FOR READ ONLY

BEGIN
    SELECT @rights = 'ALL',
           @role = 'PUBLIC'

    OPEN c_objects
    WHILE (1=1)
    BEGIN
        FETCH c_objects INTO @object_name
        IF @@SQLSTATUS <> 0 BREAK

        SELECT @time = CONVERT(VARCHAR, GetDate(), 108)
        PRINT '[%1!] hitting up object %2!', @time, @object_name
        EXECUTE('GRANT '+ @rights +' ON '+ @object_name+' TO '+@role)
    END

    PRINT '[%1!] fin!', @time

    CLOSE c_objects
    DEALLOCATE CURSOR c_objects
END
GO

GRANT ALL ON sp_grantastic TO PUBLIC
GO

ステップ2：コンポーネントを理解する

カーソルの宣言：カーソルc_objectsは、すべてのユーザーテーブル（U）、ビュー（V）、ストアドプロシージャ（P）をループするために設定されています。
権限の付与実行：カーソルを介して取得した各オブジェクトに対して、システムはGRANTコマンドを実行し、指定されたロールに定義された権利を適用します。
出力情報：プロシージャは、実行の時間と権限が付与されたオブジェクトを示すログを印刷します。

ステップ3：プロシージャを実行する

ストアドプロシージャが作成されたら、以下の簡単なコマンドで実行できます：

EXEC sp_grantastic

このコマンドを実行するだけで、データベースロールにすべてのユーザーテーブルへの必要な権限がシームレスに付与されます。

結論

あなたは、すべてのユーザーテーブルに対してデータベースロールに権限を付与する強力なSQLスクリプトを作成する方法を学びました。このアプローチは、作業の流れを合理化するだけでなく、手動による介入なしで権限を常に最新の状態に保つことを保証します。次回新しいテーブルをデータベースに追加する際には、ストアドプロシージャを再度実行するだけで済みます。それであなたは完了です。

コーディングを楽しんでください！