Grant Execute Permission To All Stored Procedures
December 5, 2007 by bnma
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