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