Re: How to give execute permissions to all stored procedures in the database?

From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 11/25/03 

Date: Mon, 24 Nov 2003 22:15:30 -0600

Try not to use system objects directly. Use information_schema if possible.
This will give you a better chance to use the same code in Yukon.

set nocount on

select 'grant execute on ' + specific_name + ' to [user]'
from information_schema.routines
where routine_type = 'PROCEDURE' 

-- 
----------------------------------------------------------------------------
-----------
Louis Davidson (drsql@hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services.  All other replies will be ignored :)
"David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in
message news:%23npLyptsDHA.1760@TK2MSFTNGP10.phx.gbl...
>
> >"Abraham" <binu_ca@yahoo.com> wrote in message
> news:u3uKWltsDHA.424@TK2MSFTNGP11.phx.gbl...
> >How to give "execute" permissions to all stored procedures in the
> database?( looking to give >permissions to all at a time )
> >Is there any database roles in SQL server ( like db_ddladmin ..)  other
> than db_owner .
>
> Here's what I do:
>
> in QA, set results as text and run
>
> set nocount on
> select 'create procedure GRANT_PERMISSIONS as'
> select 'grant execute on ' + name + ' to [user]' from sysobjects where
type
> in ('p') order by name
>
> to output a script.  Copy and run it. Then run the new GRANT_PERMISSIONS
> procedure.
>
> David
>
>