Re: EXECUTE permission denied on nested procedure call

From: Dan Guzman (danguzman@nospam-earthlink.net)
Date: 01/07/03 

From: "Dan Guzman" <danguzman@nospam-earthlink.net>
Date: Mon, 6 Jan 2003 23:58:47 -0600

I believe there's something strange going on here with the rpc call
method. AFAIK, ownership chaining should behave the same regardless of
the API used. Both objects are in the same database and owned by dbo so
the ownership chain is definitely unbroken.

Erland an I will let you know what we find out. 

--
Hope this helps.
Dan Guzman
SQL Server MVP
-----------------------
SQL FAQ links (courtesy  Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------
"Eran" <eran.librach@verizon.net> wrote in message
news:098201c2b5d9$2cc2a680$cef82ecf@TK2MSFTNGXA08...
>
> I'm glad my code could be of further use, Erland. :)
>
> The documentation for calling a stored procedure states:
> (http://msdn.microsoft.com/library/default.asp?
> url=/library/en-us/oledbsql/9_ole_20_4qm1.asp)
>
> "The RPC escape sequence provides most optimal
> performance among the three methods of calling a stored
> procedure."
>
> and:
>
> "If you know all the parameter meta data, RPC escape
> sequence is the recommended way to execute stored
> procedures."
>
> Which are the two reasons I use the RPC method.
>
> The example the docs provide for RPC calling is actually
> the basis for the code I use...
>
> ... So if this is not a bug, then either the example code
> is doing something wrong, too, or we somehow set up the
> stored procedures incorrectly. The latter, I think, is
> unlikely; the fact that it works if RPC isn't used
> suggests either a bug in the code, a bug in the RPC
> mechanism used by the provider, or a bug
> in 'sp_prepexecrpc'.
>
> At this point I don't know where else to ask, any
> suggestions?
>
> Thx,
>
> -Eran
>
>
> >-----Original Message-----
> >
> >Thanks for the code! It's not only of this interest for
> this case, but
> >I may use for a sample for OLE DB programming some time
> in the future.
> >
> >I was able to reproduce the problem on my SQL2000
> Developer Edition,
> >but I don't think this is a bug. Then again, I don't
> have the full grip
> >of ownership chains, so maybe other people can
> supplement my findings.
> >
> >What I found, using the SQL Server Profiler, is that
> behind the scenes
> >SQLOLEDB constructs a prepared query. This is the text
> for the event
> >RPC:Starting
> >
> >   declare @P1 int
> >   set @P1=NULL
> >   exec sp_prepexecrpc @P1 output, N'dbo.SP1'
> >   select @P1
> >
> >Then there is an SP:Starting event with this text:
> >
> >   exec sp_prepexecrpc
> >
> >Please don't ask me about the find details of
> sp_prepexecrpc. :-)
> >
> >I would guess, that what you really want is a true RPC
> call, and you
> >don't want any sp_prepexecrpc popping up behind your
> back. How you
> >achieve this with the SQLOLEDB provider, I don't know.
> But I seem
> >to recall having seen similar unwanted effects when
> using {} notation
> >from ADO.
> >
> >-- 
> >Erland Sommarskog, SQL Server MVP, sommar@algonet.se
> >.
> >

Relevant Pages

  • Re: EXECUTE permission denied on nested procedure call
    ... The documentation for calling a stored procedure states: ... "If you know all the parameter meta data, RPC escape ... So if this is not a bug, ...
    (microsoft.public.sqlserver.security)
  • Re: EXECUTE permission denied on nested procedure call
    ... > "If you know all the parameter meta data, RPC escape ... > sequence is the recommended way to execute stored ... > RPC isn't used suggests either a bug in the code, ...
    (microsoft.public.sqlserver.security)
  • Re: ZFS - quick start.
    ... Ok, sorry to bug again, but does this actually work or is it still an ... Does ZFS have its own nfs server or does it ... depend on having all the rpc stuff running? ...
    (freebsd-current)
  • Re: Syntax error converting datetime from from character string
    ... RPC is one of the two ways to call a stored procedure from a client. ... The other is to send an EXEC string. ...
    (microsoft.public.sqlserver.programming)
  • Re: Syntax error converting datetime from from character string
    ... convert, datetimevalue, 112)) and do not think I am getting it right. ... If you are calling your procedure from a client, you should use RPC ... client API will convert the date according to regional settings and ... pass a binary value your stored procedure. ...
    (microsoft.public.sqlserver.programming)
Quantcast