Solved

MS Access 2003/2010 - Execute SQL SP Error

Posted on 2014-01-13
4
938 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
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 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 400 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 100 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

863 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now