Grant Execute Permission To All Stored Procedures

The script:

USE SomeDB    

DECLARE @name varchar(128), @sql varchar(500)    

DECLARE procs CURSOR FAST_FORWARD FOR    

SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name ASC FOR READ ONLY    

OPEN procs    

FETCH next FROM procs INTO @name    

WHILE @@FETCH_STATUS = 0    

BEGIN    

SELECT @sql = 'Grant execute on ' + LTRIM(RTRIM(@name)) + ' to SomeUser'    

EXEC(@sql)    

PRINT @sqlFETCH next FROM procs INTO @name    

END    

CLOSE procs    

DEALLOCATE procs
Advertisements