Solved

MS Access 2003/2010 - Execute SQL SP Error

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

821 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