Re: Grant EXECUTE ALL?
From: Gerald Pena (firstname.lastname@example.org)
From: "Gerald Pena" <email@example.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.
>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
>SELECT 'GRANT EXECUTE ON ' + sysobjects.name + ' TO
>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
>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
>procedures. You need to review the grant statements very
>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
>"Gerald Pena" <firstname.lastname@example.org> wrote in
>> I would like to run a GRANT statement over a database
>> will give a user/group EXECUTE permissions to ALL SPs.
>> following is what I would like to do but the syntax is
>> correct. Can someone help with this. Thanks in advance.
>> GRANT EXECUTE on 'ALL'
>> TO dbname