Solved

only an administrator can access odbc database

Posted on 2004-07-31
7
279 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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

728 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

11 Experts available now in Live!

Get 1:1 Help Now