Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

only an administrator can access odbc database

Posted on 2004-07-31
7
Medium Priority
?
322 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 1000 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 1000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

650 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