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
Updated 4/2/2009: The code for SQL2000 is slightly different:
DECLARE @name varchar(255),
@sql varchar(255)
DECLARE curName CURSOR KEYSET FOR
select name
from sysobjects
where type = 'p'
and (name like 'sp_%' or name like '_4P%' or name like 'fp_%')
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 public'
exec (@sql)
END
CLOSE curName
DEALLOCATE curName
GO