sql server programming: 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



[quoted text, click to view]

[quoted text, click to view]

In SQL 2005 you can GRANT EXECUTE to a whole schema or whole database with a
single statement.

EG

create role MyApplicationUsers

create user MyApplicationUser without login
sp_addrolemember MyApplicationUsers, MyApplicationuser

grant execute on schema::dbo to MyApplicationUsers
go

create table t(id int)
go
create procedure p_t
as
select * from t

execute as user='MyApplicationUser'
go
select * from t
go
exec p_t
go

David

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