Solved

only an administrator can access odbc database

Posted on 2004-07-31
7
290 Views
Last Modified: 2008-03-06
Client had win2k server running SQL 2000. I installed 2003 server on a new pc, then sql 2000. Created all the user accounts. Backed up the database from the old PC, restored onto the new. I cannot set up the odbc connection, it gives an error 28000 (Login failed for user 'dispatch'), unles the user is in the administrator group, then it works fine. Both old and new SQL servers using window (only) authentication. I am not a SQL programmer, I didn't write the code. I need the users to be able to connect as just users
0
Comment
Question by:impipr2
7 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 11686879
So do you really have a windows user called dispatch or is the application trying to login using SQL Authentication?
0
 

Author Comment

by:impipr2
ID: 11687528
Yes, that is a valid user. They can log onto Terminal services properly and/or access/write the server shares
0
 

Author Comment

by:impipr2
ID: 11693203
To clarify: when I am setting up ODBC for a non administrator,  I click add, then SQL server. I fill in the name and description, select the correct server in the drop down. Select windows authentication, then next.

Now on "change the default database to:" This database is not listed. Master, msdb, northwind, etc... They are there, but my database is not.

If I chage the user to an administrator, they can connect to it just fine.
0
 
LVL 10

Accepted Solution

by:
AustinSeven earned 250 total points
ID: 11694338
I'm wondering about your description of events as 'restored database' is last.   Given that BUILTIN\Administrators can get access, it sounds like you have not create the Login and map it as a user in the restored db?   If you are only using Windows authentication, you should have a SQL Login called domain\dispatch (where domain is either the local server or network domain) and you should see this under 'users' within the database.   In the Login at the server level, you should also allocate the appropriate database roles - eg. db_owner or whatever.  
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 250 total points
ID: 11694926
I will assume that both the old and new server are/were just member servers not the PDC/BDC/ADC.

In the enterprise manager (EM) - > %ServerName% -> Security -> Logins the user Id's should look something like %DomainName%\UserID and not just UserID. If it is just the userid or %ServerName%\UserID the userID is not the same one that is being used by the domain. Make sure you include user groups as well as individual userid's

After confirming that the server level user ids are correct, you can then attempt to logon again. If you are running the servers without a domain (i.e. %ServerName%\UserID) And you are creating all the accounts on each server then you will probably still not be able to access the database. This is because each server has a unique SID (security identification numbers) for the user from one machine to another.

If you have no domain, or cannot access the database after covering the steps above there are 2 methods left.
Method 1 sometimes works:
Go to the URL and download and create the sp_sidmap procedure and execute it.
http://support.microsoft.com/default.aspx?scid=kb;en-us;298897&Product=sql2k

Method 2:
1. Go into the EM -> %Server% -> Database -> %DBName% -> Security
2. Delete all the users that are listed there that you want to restore access to.
3. Do a refresh to ensure the UserID is no lnger there.
4. Go back to the  %ServerName% -> Security level.
5. Open each userid's Property and select the "Database Access" tab.
6. Check the %DBName% checkbox on and assign them the appropriate Database Role in the lower window.

Good luck.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

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.
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

948 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

23 Experts available now in Live!

Get 1:1 Help Now