Solved

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

Posted on 2009-05-19
7
253 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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.

707 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

15 Experts available now in Live!

Get 1:1 Help Now