65.9K
CodeProject is changing. Read more.
Home

Grant Execute on all procedures to a user

starIconstarIconstarIcon
emptyStarIcon
starIcon
emptyStarIcon

3.58/5 (11 votes)

Jan 15, 2004

viewsIcon

82702

downloadIcon

323

This stored procedure grants execute privilege on all procedures to specified user

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.