Microsoft KB Archive/180853

= PRB: Stored Procedure Fails if Rights Are Revoked and Regranted =

Article ID: 180853

Article Last Modified on 12/3/2003

-

APPLIES TO


 * Microsoft Open Database Connectivity 2.0
 * Microsoft Open Database Connectivity 2.0
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Data Access Components 2.0
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6

-



This article was previously published under Q180853



SYMPTOMS
The Microsoft Oracle driver returns the following error message when user rights are granted and then revoked on a table accessed by a stored procedure:

Return: SQL_ERROR=-1

szErrorMsg="[Microsoft][ODBC driver for Oracle]Wrong number of parameters"

szErrorMsg="[Microsoft][ODBC driver for Oracle]Syntax error or access violation"



CAUSE
The call to the Oracle OCI function odessp fails in this scenario, but is needed in order to implement default parameters.



RESOLUTION
You must recompile your stored procedure after modifying underlying table permissions before you run the stored procedure again.



STATUS
This is a problem with the return value from the odessp Oracle OCI function, not with the Microsoft Oracle ODBC driver.

This behavior is by design.



Steps to Reproduce Behavior
 Create table table1.test(c1 varchar(20)). Grant user2 "INSERT" privileges to table1.test.  Create or replace procedure user2.sp_intest(p1 in varchar) as      Begin INSERT INTO user1.test values ( p1); end;  Execute user2.sp_intest to make sure the procedure works. Revoke "INSERT" privileges on table1.test to user2.</li> Grant "INSERT" privileges on table1.test to user2.</li> Execute user2.sp_intest(p1).</li></ol>

Additional query words: ADO, ODBC, Oracle, ASP, Stored Procedure, Security

Keywords: kbnofix kboracle kbdatabase kbprb kbdriver KB180853

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.