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.

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here