Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 180853 - Last Review: December 3, 2003 - Revision: 3.1
PRB: Stored Procedure Fails if Rights Are Revoked and Regranted
This article was previously published under Q180853
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"
The call to the Oracle OCI function odessp() fails in this scenario, but is
needed in order to implement default parameters.
You must recompile your stored procedure after modifying underlying table
permissions before you run the stored procedure again.
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.
- Grant "INSERT" privileges on table1.test to user2.
- Execute user2.sp_intest(p1).
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
| kbdatabase kbdriver kbnofix kboracle kbprb KB180853 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate