Very Computer

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.

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.

Very Computer

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

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

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