Patrick
2/28/2007 9:10:00 PM
This might help you, I am using for same purpose
CREATE procedure up_GrantExecute
(
@User varchar(25) = 'db_executor',
@Force bit = 0
)
as
set nocount on
declare @Name varchar(100),
@Command varchar(255),
@uid int
declare @ProcCount int
set @ProcCount = 0
select @Name = min([name])
from sysobjects
where type in ('P', 'FN', 'IF') and
left([name],3) <> 'dt_' and
[name] <> 'up_GrantExecute'
while @Name is not null begin
select @uid = uid
from sysusers
where [name] = @User
if not exists (select * from sysprotects where id = object_id(@Name)
and
action = 224 and uid = @uid) or
@Force = 1 begin
set @Command = 'grant execute on ' + @Name + ' to ' + @User
print @Command
set @ProcCount = @ProcCount + 1
exec (@Command)
end
select @Name = min(name)
from sysobjects
where type in ('P', 'FN', 'IF') and
left([name],3) <> 'dt_' and
[name] <> 'up_GrantExecute' and
[name] > @Name
end
if @ProcCount = 0 begin
print 'no new objects found'
end
return
GO
On Feb 28, 12:59 pm, Simon Harvey <notha...@hotmail.com> wrote:
> Hi All,
>
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
>
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
>
> Surely I don't have to go through every SProc one by one?
>
> Please, please say it aint so!
>
> :-(
>
> Thanks
>
> Simon