Re: Grant EXECUTE ALL?

From: Gerald Pena (gerald.pena@farmcreditbank.com)
Date: 03/18/03 

From: "Gerald Pena" <gerald.pena@farmcreditbank.com>
Date: Tue, 18 Mar 2003 11:35:00 -0800

I think you have given me exactly what I need. Thanks so
much! I may be able to get around the system SPs by using
a where category <> 2. If I rememeber right this is the
code for a system SP. Thanks again for the quick help.

>-----Original Message-----
>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?
    ... work around the problem by runnning a query to generate the GRANT EXECUTE ... You need to review the grant statements very carefully. ...
    (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: configuring oracle database gateway drda
    ... The Oracle syntax is: GRANT EXECUTE ON oraclegtw TO PUBLIC; ...
    (comp.databases.oracle.server)