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.
Sumit Amar started programming in BASIC at the age of 14 in 1993, then moved on to C/UNIX.
Later in 1999, he started developing commercial applications in J2SE, J2EE and Perl. He started developing applications in .NET with ASP+ (later renamed to ASP.NET) in December 2000 with public Beta 1. He has been developing in .NET ever since.
He has an MBA degree in IT and Systems.
Sumit is a Director of Engineering at Electronic Arts, where he works on building hybrid cloud systems.