[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

MS Access 2003/2010 - Execute SQL SP Error

Posted on 2014-01-13
4
Medium Priority
?
993 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

650 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