?
Solved

only an administrator can access odbc database

Posted on 2004-07-31
7
Medium Priority
?
324 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
5 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

862 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