Grant Execute, stored procs, and variables.....

Grant Execute, stored procs, and variables.....

Post by Patrick Purvianc » Fri, 18 May 2001 04:27:40



I can't seem to find any documentation on whether or not you can include
variables or in-line select statements with the Grant Execute function.  I
am trying to create a Stored Proc to grant execute rights on all user
created stored procedures to a specific user.  Seems straight-forward, but
the procedure should receive this user from a VB app.  The parameter would
be passed to the Stored Proc by the app in a call to the Stored Proc.  Is
there a way to create the Stored Proc with the Grant Execute AND a variable
parameter?

My issue seems to be the Grant Execute function.  I cannot get the procedure
to accept a variable in place of the user_name or an in-line Select
statement

Here's the SP that granting the execute rights:
Grant Execute On (Select [name] From sysobjects Where [xtype]='P')
To [Processors]

Where [Processors] is a database role.

The other SP I'm working on is:





from the VB app.

Any help is greatly appreciated.  Willing to read up if you'll direct me to
the right place!

Thanks,

Patrick Purviance
Associated Systems, Inc.

 
 
 

Grant Execute, stored procs, and variables.....

Post by Hirantha S Hettiarachch » Fri, 18 May 2001 05:06:13


Patrick,
you can use dynamic sql. but it has its own problems.
like





this might help about dynamic sql.
http://www.algonet.se/~sommar/dynamic_sql.html

--
hth
~~~~~~~~~~~~~~~~~~~~~~~~~~
Hirantha S Hettiarachchi
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> I can't seem to find any documentation on whether or not you can include
> variables or in-line select statements with the Grant Execute function.  I
> am trying to create a Stored Proc to grant execute rights on all user
> created stored procedures to a specific user.  Seems straight-forward, but
> the procedure should receive this user from a VB app.  The parameter would
> be passed to the Stored Proc by the app in a call to the Stored Proc.  Is
> there a way to create the Stored Proc with the Grant Execute AND a
variable
> parameter?

> My issue seems to be the Grant Execute function.  I cannot get the
procedure
> to accept a variable in place of the user_name or an in-line Select
> statement

> Here's the SP that granting the execute rights:
> Grant Execute On (Select [name] From sysobjects Where [xtype]='P')
> To [Processors]

> Where [Processors] is a database role.

> The other SP I'm working on is:





> from the VB app.

> Any help is greatly appreciated.  Willing to read up if you'll direct me
to
> the right place!

> Thanks,

> Patrick Purviance
> Associated Systems, Inc.


 
 
 

1. Granting execute to all procs with a role?

I am using SQL Server 2000 and am familiar with fixed database roles that
allow me to, for example, read from all tables whether they are created in
the past, or in the future..

I need to grant a user execute rights to all stored procedures.  Is there a
similar fixed database role I can use?  I don't think so, so if any of you
have another clever idea, I would love to hear it.  In essence, I want to
grant a user execute rights today against all procedures that are created in
the future.

I know I can easily script the grants against all the existing procedures,
that is not what I am concerned about.  I am concerned that tomorrow one of
the developers will undoubtedly create a new procedure and forget to issue
the permissions.  Is there a way I can avoid the hassle of maintaining this
on an ongoing basis (other than the obvious of hoping the developers assign
the rights as they create the objects)?

Thanks.

2. pgsql/doc/src/sgml user-manag.sgml

3. Stored procs for adding, dropping, granting, revoking

4. Queries including a " ' "

5. Packages, stored procs and grants...

6. Universal access to databases

7. create proc grant execute with grant

8. Load Individual Table

9. Executing a sql statement stored as a variable in a stored Procedure

10. Grant execute on stored procedures.

11. Stored procs in an Execute SQL Task

12. grant execute on user stored proc

13. How to execute stored procs from A97 client