2008年6月17日火曜日

SQLServer ストアドの実行権限を与えるストアド

通常はストアドを作成後、実行できるユーザを設定します。
しかしDBを他のサーバに移した場合など再設定が必要な場合があります。
ひとつずつ設定するのは面倒なので、指定ユーザを一気に設定するストアドを作ってしまおう。
※エラー処理は省略しています。必要な方はご自分で考えてみてください。

CREATE PROCEDURE [dbo].[GRANT_ADD]
 (
   --パラメータ 許可を与えるユーザーを指定します(DBに存在しログインできる状態のユーザ)
   @UserName NVARCHAR(256) = ''
 )
AS
--[DBユーザ]に実行権限を与える

 SET NOCOUNT ON

 --変数定義
 DECLARE @objectname NVARCHAR(256), @cur_cnt INT, @loop_cnt INT
 DECLARE @sql NVARCHAR(100)

 --カーソル定義(ストアド名を抽出)
 DECLARE Cur CURSOR LOCAL SCROLL STATIC
 FOR
 SELECT NAME FROM sysobjects WHERE OBJECTPROPERTY(id, N'IsProcedure') = 1
                             AND name NOT LIKE 'dt_%'

 --カーソルをオープンする
 OPEN Cur

 SET @loop_cnt = 0

 IF (@@CURSOR_ROWS <> 0)
 BEGIN

  --@@が付くときはグローバルなので念のため別途保持しておく
  SET @cur_cnt = @@CURSOR_ROWS
  
  WHILE (@cur_cnt <> @loop_cnt)
  BEGIN
    --ストアド名を@objectnameへ
    FETCH NEXT FROM Cur INTO @objectname

    --実行権限を与えるSQLの発行
    SET @sql = 'GRANT EXECUTE ON [dbo].[' + @objectname + '] TO [' + @UserName + ']'
    --SQL実行
    EXEC sp_executesql @sql

    --次のストアドへ
    SET @loop_cnt = @loop_cnt + 1
  END
 END
 ELSE
 BEGIN
   PRINT 'ストアドが見つかりません'
 END

----------------------------------------------------------------------------------

あとは以下を実行して完了!(ただしログインユーザは先に作っておいてね)
EXEC GRANT_ADD @UserName = 'ExecUser'

※ExecUserは自分の環境に合わせてください。

0 件のコメント:

Google検索