Granting right to all the user sp's

Hello,
I would like to ease granting the right to execute all user sp's by normal u
sers.
By default, AFIK you have to click the EXECUTE option of each and every sp.
I head about a procedure, which could automate this.
Anyone knows this kind of procedure or how to to this?
Thank You
JoachimHi,
use pubs
go
select 'grant execute on ' +name +' to user_name' from sysobjects where
type='p'
Replace the database name and user_name based on your requirement.
After excuting the script you will get a bunch of grant statement in your
result pane, just cut and paste the entire contents and execute it again.
Thanks
Hari
MCDBA
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:404C5069.C80FF06E@.freenet.de...
> Hello,
> I would like to ease granting the right to execute all user sp's by normal
users.
> By default, AFIK you have to click the EXECUTE option of each and every
sp.
> I head about a procedure, which could automate this.
> Anyone knows this kind of procedure or how to to this?
> Thank You
> Joachim|||You can use a procedure like this
use master
go
create procedure sp_grantexec(@.user sysname,@.debug int = 0)
as
set nocount on
declare @.ret int
declare @.sql nvarchar(4000)
declare @.db sysname ; set @.db = DB_NAME()
declare @.u sysname ; set @.u = QUOTENAME(@.user)
-- check user exists
if not exists(select * from sysusers where name = @.user)
begin
raiserror('User %s is not a valid user in this database',16,1,@.user)
return -1
end
set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'
'IsMSShipped'') =
0'
if @.debug = 1 print @.sql
exec @.ret = master.dbo.xp_execresultset @.sql,@.db
If @.ret <> 0
begin
raiserror('Error executing command %s',16,1,@.sql)
return -2
end
go
Then you can run it like below to grant user foo exec permissions on all
user stored procedures in the pubs database
use pubs
go
exec sp_grantexec 'foo'
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:404C5069.C80FF06E@.freenet.de...
> Hello,
> I would like to ease granting the right to execute all user sp's by normal
users.
> By default, AFIK you have to click the EXECUTE option of each and every
sp.
> I head about a procedure, which could automate this.
> Anyone knows this kind of procedure or how to to this?
> Thank You
> Joachim