Grant Execute on all procedures to a user

• Download source files - 1 Kb

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.