MS SQl Server 為使用者批量授與預存程序執行許可權

set nocount on
declare @procedure sysname  --用於獲得預存程序名集
,@str Nvarchar(200)         --設定固定變數，僅為方便用
,@str1  Nvarchar(200)       --存執行串，用於迴圈操作
,@UserName Nvarchar(30)     --所要授權的使用者名稱
set @str = N'grant execute on '
set @UserName = N'BKPR'
declare Authorization_pro cursor for
select SCH.name +'.'+ pro.name
from sys.procedures  pro join sys.schemas sch on pro.schema_id = sch.schema_id
open Authorization_pro
Fetch next from Authorization_pro into @procedure
while @@FETCH_STATUS =0
begin
set @str1 = @str + @procedure + ' to ' + @UserName
execute (@str1)
set @str1 = ''
fetch next from Authorization_pro into @procedure
end
close Authorization_pro
deallocate Authorization_pro
set nocount off