?
Solved

MS Access 2003/2010 - Execute SQL SP Error

Posted on 2014-01-13
4
Medium Priority
?
981 Views
Last Modified: 2014-01-15
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.
0
Comment
Question by:holemania
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39777930
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
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1600 total points
ID: 39778917
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
 
LVL 24

Assisted Solution

by:Bitsqueezer
Bitsqueezer earned 400 total points
ID: 39781438
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
 

Author Closing Comment

by:holemania
ID: 39782834
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

Featured Post

Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Viewers will learn how the fundamental information of how to create a table.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question