only an administrator can access odbc database

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
Who is Participating?
AustinSevenConnect With a Mentor Commented:
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.  
So do you really have a windows user called dispatch or is the application trying to login using SQL Authentication?
impipr2Author Commented:
Yes, that is a valid user. They can log onto Terminal services properly and/or access/write the server shares
impipr2Author Commented:
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.
Jim P.Connect With a Mentor Commented:
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.;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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.