Re: Grant EXECUTE ALL?

From: SQL Server Development Team [MSFT] (sqldev@microsoft.com)
Date: 03/18/03 

From: "SQL Server Development Team [MSFT]" <sqldev@microsoft.com>
Date: Tue, 18 Mar 2003 10:58:51 -0800

Hi Gerald
While the GRANT statment does not support ALL stored procedures, you can
work around the problem by runnning a query to generate the GRANT EXECUTE
statements. Using Query Analzyer, execute the following query in your
target database.

SELECT 'GRANT EXECUTE ON ' + sysobjects.name + ' TO <group_or_role>' +
CHAR(10) + CHAR(13) + 'GO' + CHAR(10) + CHAR(13)
  FROM sysobjects
 WHERE type = 'P'

Then copy and paste the result set of the query in a new query window.
Review the GRANT statements and execute again.

One caveat to this solution is that it will grant execute for ALL stored
procedures in your database to the given role including system stored
procedures. You need to review the grant statements very carefully.

Good luck,
Michael 

--
SQL Server Development Team
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"Gerald Pena" <gerald.pena@farmcreditbank.com> wrote in message
news:130501c2ed76$ba33dc80$a101280a@phx.gbl...
> I would like to run a GRANT statement over a database that
> will give a user/group EXECUTE permissions to ALL SPs. The
> following is what I would like to do but the syntax is not
> correct. Can someone help with this. Thanks in advance.
>
> GRANT EXECUTE on 'ALL'
> TO dbname
>
>

Relevant Pages

  • Re: Grant EXECUTE ALL?
    ... the GRANT EXECUTE ... Using Query Analzyer, execute the following ... >Review the GRANT statements and execute again. ... >One caveat to this solution is that it will grant execute ...
    (microsoft.public.sqlserver.security)
  • Re: Query Results when Sum is included
    ... You need to use a Left Join in your query, ... > My app has tblGrantProgram with a one to many relationship with tblGrants. ... > am trying to create a query which yields one record for each grant program ... > Can someone help me to get all of the programs to display along with the sum? ...
    (microsoft.public.access.queries)
  • Re: Query Results when Sum is included
    ... > You need to use a Left Join in your query, ... > Steve Schapel, Microsoft Access MVP ... >> My app has tblGrantProgram with a one to many relationship with tblGrants. ... >> tblGrants) for each grant program. ...
    (microsoft.public.access.queries)
  • Re: Loop for Table
    ... In query design view, add a calculated field ... and then use the query wherever you want the concatentated Full Grant ... >Is it possible to create a loop that will open my linked ... John Nurick [Microsoft Access MVP] ...
    (microsoft.public.access.tablesdbdesign)
  • Re: permission denied on object sp_sdidebug, database dbname,
    ... When I installed ms sql server with sp3 I found I could not run the debug ... I needed to GRANT to run the debugger logged in as this user. ... debugger on stored procedures. ... >Why do you need to GRANT execute on this sp? ...
    (microsoft.public.sqlserver.programming)