Assigning Execute Permissions to All My Stored Procedures

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
SimonThis 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|||"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:udBYCt3WHHA.3568@.TK2MSFTNGP06.phx.gbl...
> 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!
>
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|||Thanks guys!|||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
Here's a low maintenance approach.
On SQL Server, we can generate a bunch of GRANT SQL statements then
execute them:
Select 'Grant Execute On [' + o.name + '] To ' + UserNameGoesHere +
';'
>From sysobjects o
Where o.type = 'P'
Execute the above stmt
Copy results from your output window to another window
And execute all.
* SQL statement type from memory. I currently don't have SQL Server
installed.
Quoc Linh|||Simon,
In SQL 2005 you can grant execute on the schema to which the objects belong.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"quoclinh" <lequoclinh@.yahoo.com> wrote in message
news:1173382910.595017.243110@.q40g2000cwq.googlegroups.com...
> 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
> Here's a low maintenance approach.
> On SQL Server, we can generate a bunch of GRANT SQL statements then
> execute them:
> Select 'Grant Execute On [' + o.name + '] To ' + UserNameGoesHere +
> ';'
>>From sysobjects o
> Where o.type = 'P'
> Execute the above stmt
> Copy results from your output window to another window
> And execute all.
> * SQL statement type from memory. I currently don't have SQL Server
> installed.
> Quoc Linh
>