DECLARE @name varchar(255),
@sql varchar(255)
DECLARE curName CURSOR KEYSET FOR
select name
from sys.objects
where type = 'p'
OPEN curName
WHILE 1=1
BEGIN
FETCH NEXT FROM curName INTO @name
if @@fetch_status <> 0 begin
break
end
SET @sql = 'grant exec on ' + @name + ' to chemsys'
exec (@sql)
END
CLOSE curName
DEALLOCATE curName
GO