Re: EXECUTE permission denied on nested procedure call

From: Erland Sommarskog (sommar@algonet.se)
Date: 01/06/03 

From: Erland Sommarskog <sommar@algonet.se>
Date: Mon, 6 Jan 2003 21:43:15 +0000 (UTC)

[posted and mailed]

Eran (eran.librach@verizon.net) writes:
> Enclosed you'll find sample code for a small console app
> which tries to execute SP1. It reproduces the problem
> once run from the user account granted EXECUTE permission
> on SP1.
>
> It assumes a stored procedure named 'SP1' exists in a
> catalog 'SBS_dev' on 'apollyon\VSdotNET'. You can change
> these constants easily in the function 'connectToDB'.
>
> If you prefer, I can send the executable itself. Just let
> me know which data-source to connect to.

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
    ... Enclosed you'll find sample code for a small console app ... which tries to execute SP1. ... >from a DB-Library client, both as a direct SQL statement ...
    (microsoft.public.sqlserver.security)
  • Re: DTExec slow
    ... It was actually this way before SP1 as well. ... The thing is that when you execute a package, CryptoAPI, on behalf of the SSIS service, attempts to go out to the internet to check a certificate revocation list. ...
    (microsoft.public.sqlserver.dts)
  • Re: Reading SCRIPT_FILENAME thru cron
    ... $sp1 is set correctly when I execute file.php thru the browser. ... when I run that script thru cron $sp1 is always null. ...
    (comp.lang.php)
  • Re: Executing system commands with wxpython
    ... > the output somewhere in the same window. ... you'll use the same Python code to execute system commands ... # -- Begin sample code ... # Lay out the button and edit area: ...
    (comp.lang.python)
  • Re: EXECUTE permission denied on nested procedure call
    ... I am using OLE DB, ... ALTER PROCEDURE dbo.SP1 ... grant EXECUTE on dbo.SP1 to rSales ... I get the same result when executing SP1 as that user. ...
    (microsoft.public.sqlserver.security)