Solved

Permissions problem between access 2000 and sql server 2005 with stored procedures

Posted on 2009-05-19
7
254 Views
Last Modified: 2013-11-28
We recently updated our database from sql server 7 to sql server 2005. We use access as a frontend for our main application which exists on a terminal server.

When I log into the terminal server as an admin and open the application; the stored procedures work fine. When I log in as a normal user most of the storedprocedures fail. Also when I am logged in as a user access renames the stored procedures and adds a ";1" at the end of them or adds a prefix of "fn" or "dt".

Obviously this is some sort of permissions issue, however we do not know where to change the permissions for this.
0
Comment
Question by:kikimayhay
7 Comments
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 200 total points
ID: 24422581
While creating the procedure, use EXECUTE as OWNER keywords to get rid of failing execution of Stored Procedures.

Or else you can issue on all procedures

GRANT EXECUTE ON procedure_name TO user_name;
GO
0
 
LVL 20

Assisted Solution

by:Marten Rune
Marten Rune earned 200 total points
ID: 24422597
0
 
LVL 10

Assisted Solution

by:dwe761
dwe761 earned 100 total points
ID: 24422635
I'll try to help you but can you give a bit more info?  Do you know if you are using Windows Authentication or do you use SQL Logins?

Are you using Access ADP's or MDB or MDE or???

What permissions are you using on the Access side?

Where are the stored procedures being renamed?
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:kikimayhay
ID: 24422700
We use mixed mode authentication but in this case all of the users are getting access through windows authentication.
We use ADP's
The users login into access via a login in screen (username and password is identical to their windows login) so in essence windows authentication once again to access the sql.
The only place the stored procedures names change is in the access adp logged in as a user. If I log in as myself (administrator) the stored procedures are named correctly.
0
 

Author Comment

by:kikimayhay
ID: 24422845
Trying to follow link from Microsoft from Marten Rue link from above and link no longer exists.
0
 

Accepted Solution

by:
kikimayhay earned 0 total points
ID: 24423233
We seem to have stumbled across the issue apparently the issue is more centered around one of our terminal servers acting up (will be completely rebuilt in the next day) not anything to do with permissions.
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 24424550
Glad to hear you solved it.

If you want to read up on the security model.
Here is another link: http://msdn.microsoft.com/en-us/library/ms189121.aspx

/Marten
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sending email from List Data 2 51
count all and uniques records in a table 20 28
Field Size - Double?  Want to display 0 5 30
Sql query 107 29
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

867 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

12 Experts available now in Live!

Get 1:1 Help Now