Introduction
Granting execute privileges on all procedures to a restricted user, can be an issue. However, by retrieving the meta data information from the sysobjects, a simple loop can emulate this process and grant the execute on all available procedures to a specified user.
CREATE PROC grants as
declare curse cursor for select name from sysobjects where type='P'
OPEN CURSE
declare @proc varchar(100)
declare @stmt nvarchar(200)
fetch next from curse into @proc
while @@fetch_status=0
begin
set @stmt='grant execute on '+@proc+' to bnbuser'
exec SP_EXECUTESQL @STMT
print @stmt
fetch next from curse into @proc
end
close curse
deallocate curse
GO
After the compilation, just write grants in the query analyser to automate the grant process.
