MS Access 2003/2010 - Execute SQL SP Error

I have a stored procedure that I created for a user to use from within Microsoft Access 2010.  If I log in as "sa", I can execute the stored procedure fine.  However, if the user logs in and run the stored procedure, she gets the following error:


Reserved error (-7711); there is no message for this error.

I grant the user the ability to execute this stored procedure.  So not sure what I'm missing.  If I log onto the ODBC connection string as "sa", then it runs fine.  However, if user logs in as herself, then get the above error.  Also user can run a "Select" statement fine with the same ODBC connection string.  So I think it may be some kind of stored procedure permission.
holemaniaAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Can you post the code for the Stored Procedure?

Also note that while you may have provided permission for the user to Execute the SP, you must still insure that all components of that SP allow the user to perform the actions. For example, if I have a SP where I'm updating tblInvoice, then my users must have sufficient permissions to perform that action.
0
 
Surendra NathTechnology LeadCommented:
looks like there is a hotfix available for this
try to download this and install and let us know if it does not work

http://support.microsoft.com/kb/884404
0
 
BitsqueezerConnect With a Mentor Commented:
Hi,

and to extend what Scott said above: You can add a "WITH EXECUTE AS" clause to the stored procedure and add a user which is allowed to use all the resources of the SP.  On that way you can ensure that the user can execute the SP without giving her additional rights to the used resources.
It is a good idea to create a pure database user for that purpose on the SQL Server (if you have mixed mode authorization enabled). Such user has no corresponding login user on SQL Server level and so this user can be used to execute such SPs only without the possibility that this user with the higher permissions can be used to login to the database. Additionally you can create such pure database users simply as db owner and you don't need to be sysadmin.

Cheers,

Christian
0
 
holemaniaAuthor Commented:
Thanks.  Found out there's a function that also execute and user need access to that function.  With what Scott suggested, I was able to figure that out.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.