Granting EXECUTE privilege on group of stored procedures to a specified user

  • Article

CREATE proc grants(@procs varchar(100),@user varchar(100)) as
declare curse cursor for select name from sysobjects where type='P' and name like @procs

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 '+@user
exec SP_EXECUTESQL @STMT
print @stmt
fetch next from curse into @proc
end

close curse
deallocate curse
GO

grants 'sptest%','bnbuser'
go