?
Solved

Domain User receives ODBC errors while other Domain users use MS Access Database without issue on the same Workstation

Posted on 2006-04-14
8
Medium Priority
?
329 Views
Last Modified: 2009-07-29
My users are using Domain authentication to access their SQL Server Database using a Microsoft Access Front End.
 
Recently a new workstation was added (and new user added to the domain).  Everyone is able to log on to the MS Access database and everything works great except for the most recent user added to the Domain.  When the new user logs on and attempts to use the MS Access Database they receive lots of ODBC errors for minor things like adding a record.

Whether it's an easy fix or not I don't know but it is urgent so 500 points and my deepest gratitude for anyone who can help me understand where the problem lies.

Thanks in advance for the stellar help I'm confident will come.
0
Comment
Question by:Rick_Rickards
  • 4
  • 4
8 Comments
 
LVL 15

Expert Comment

by:m1tk4
ID: 16458287
1. Make sure the new workstation has the latest MDAC installed.

2. Try logging in as that new user on an older workstation that is proven to work and use Access - basically to determine whether it is a problem with this user's account or the new workstation.
0
 
LVL 16

Author Comment

by:Rick_Rickards
ID: 16458374
1) I'm not sure how to check the latest MDAC version for the workstation in question (tips?)

2) I did try logging in as the new user on an older workstation that is proven to work and uses Access (same MS Access App, Access Version etc as the one before.)  Like the new workatation it produces the same ODBC errors but only if I log on using the new users logon information.

Any ideas?
0
 
LVL 15

Expert Comment

by:m1tk4
ID: 16458461
1) Just try installing the latest one - it's a free download from Microsoft: http://www.microsoft.com/downloads/details.aspx?FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c&DisplayLang=en (If the link doesn't work just search for MDAC at Microsoft). If you already have the latest it will tell you you don't need to upgrade. However, because of your 2) answer I don't think MDAC is your problem.

2) In Enterprise Manager, go to your Database / Users, can you confirm that your new user is added there and that the permissions and roles he/she has are the same as everybody else's?

3) Please provide a sample of ODBC error (full text)
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 16

Author Comment

by:Rick_Rickards
ID: 16458522
1) I was able to download and attempt (unsuccessfully) to install the MDAC driver suggested.  If it helps, I'm running Windows Service Pack 2 and the error occurs on both machines.  The error message it gave me was...

mdac 2.8 RTM is incompatible with this version of windows.  All of its features are currently part of windows.

2) The user is added in Enterprise manager and assigned the same roles as everyone else, those being (public and db_owner).  One notable difference is that it won't let me just add the user by their name as is the case for everyone else.  In this users case it insists on adding the domain name in front of the user name reading...

DOMAINNAME\username

instead of just

username

3) Here is the ODBC Error message in full.  If it helps the table that it is attempting to save a record to is named ARInvoice.  Here is the Error.

run-time error '3155':

ODBC--insert on a linked tble 'ARInvoice' failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]The current user is not the database or object owner of table 'tempdb..tblIdentity_Save'.
Cannot perform SET Operation. (#8104)

*****

Any ideas, I'm very willing and eager to chase down any of your theories having exhausted all of my own.
0
 
LVL 15

Expert Comment

by:m1tk4
ID: 16458682
1) That's normal. Let's leave MDAC aside, it's not the problem.

2)3):

Try deleting this record and re-adding him (without the domain name) in Security/Logins in Enterprise manager first, then adding again to the database.

Check that the default database for this user is the one you are using.

When you log in as that new user on a remote PC, are you logging into the domain or just the local PC?

Is your SQL server the domain controller as well? If not, try creating a LOCAL user on the SQL server with the same name and password as your new user.

0
 
LVL 16

Author Comment

by:Rick_Rickards
ID: 16458745
For a moment I thought we were really on to something.

I deleted the use listed under the database and then went to the user list for the SQL server itself discovering that the user in questoin was already set up there.  I gave her permissions to the database as configured for everone else and she immediately appeared as one of the users for the database itself.  It looked so right I was shocked to find that it did not fix the problem.  Same ODBC error as before, but even so I'm convinced we've fixed something that wasn't right to beging with.

The default database for the user is the databae that I am using.

When I log into the user for the PC I am logging onto the domain.  If I wasn't I would not be so readily availed of all the domain shares.

The SQL Server and the Domain controler are in fact one in the same machine.

Just to cover all bases I deleted the user from the SQL server entirely and set up everything from scratch (wanted to be sure nothing was missed).  In the end I ended up back where I started, same ODBC errors.

Any other ideas?


0
 
LVL 15

Accepted Solution

by:
m1tk4 earned 2000 total points
ID: 16458830
In Security/Logins, is there any Group set up as Windows Group that this user may not be a member of?

After you recreated the user account in SQL, did you log off /log on the user on a remote PC?
0
 
LVL 16

Author Comment

by:Rick_Rickards
ID: 16458900
That was it.  By going into the Domain Users and Adding them to the right Group the problem was solved.  

My Sincere thanks!!!

Rick
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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

809 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