我們可以利用 sysobjects 系統表, 利用 xtype='P' 來取出所有的 stored procedure 名稱, 再利用 GRANT 指令給予權限, 語法如下,
GRANT EXECUTE ON [dbo].[sp_xxxx] TO [user_loginame]
如此只要利用一個 cursor 配合 sp_executesql 就能快速地給予 user 執行權限囉, 程式如下,
代碼: 選擇全部
declare @name nvarchar(255), @cmd nvarchar(1000), @loginame nvarchar(200)
select @loginame = 'user_loginame'
declare cursor_sp cursor for
select name from sysobjects where xtype='P'
open cursor_sp
fetch next from cursor_sp into @name
while @@fetch_status=0
begin
select @cmd = 'GRANT EXECUTE on ['+@name+'] TO ['+@loginame+']'
--print @cmd
exec sp_executesql @cmd
fetch next from cursor_sp into @name
end
close cursor_sp
deallocate cursor_sp