User's Windows profile prevents user from authenticating to MS SQL Server

Question

I have a user who cannot login to a .mdb when he is logged in to Windows, on his workstation or any other.  No one can login to the .mdb when he is logged in to Windows.

He CAN login to the same .mdb if someone else is logged in to Windows, either on his workstation or any other. 

When the user logs in he gets the following error:

MS SQL Server Login
Connection Failed:
SQL State: ‘28000’
SQL Server Error 18456
[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user ‘domain\username’

I have removed and recreated the ODBC Connection while he is logged in.  I have tried changing the trusted login to administrator (in the window that pops up when he fails to connect).

He has the permissions he needs to the .mdb because he can login under anyone else’s profile.

I tried copying his profile to a brand new user and ended up with the same result.

This leads me to wonder if there is some corruption in one of his profile’s .dat files or ???

This is the boss’ account and he does not want to be given a new username and profile… 

Microsoft SQL Server  2000 - 8.00.760 (Intel X86)

Running on MS Windows 2003 Server Standard SP1 

All workstations are XP SP2 with MS Access 2003 SP2 (from Office Pro distro)

We use roaming profiles.  All systems within the same physical and logical network and domain.

Any help you can provide will be greatly appreciated.
Please let me know if there is any additional information you need.

Thanks,

Mike

Answer 1

Have you tried executing:

sp_grantlogin 'domain\username'

to allow the user to connect to SQL Server?

Thanks
Laurentiu

Answer 2

Laurentiu,

Thank you for your response.  I apologize for not responding more promptly.  I am a part time contractor here and only work here 3 days a week (busy running around on other days...)

I am not a SQL Admin and don't know where to execute the command you mentioned.
the command prompt does not recognize sp_grantlogin while at the root c:\.

If you can give me more information I will be happy to try your recommendations.

Thanks,

Mirby

Answer 3

Hi,

I don´t know if you are aware of that, but the most recent service pack for SQL Server is  SP4, as of your version posted, you are on SP3a. This is not related to the problem but should be evaluated to be applied.

Now to your problem, as you have SQL 2k in place, but you did not mentioned if its the MSDE version or another full SKU, I assume the full SKU. Open up Query Analyzer and connect with a valid login. if have both integrated and SQL Server Authentication activated, you can choose WIndows Authentication (Which will grab the current user logged on to the system) or use SQL Server Authentication,which has nothing to do with WIndows Authentication and only uses user which were created in SQL Server, so typing in here any Windows user won´t help as they are authenticated differently. If you are connected , switch the database (dropdown at the top of the GUI), type in the command mentioned by Laurentio and press F5. That should do the job.

HTH, jens Suessmeyer.

---
http://www.sqlserver2005.de
---

Answer 4

Thank you Jens,

I was able to follow the procedure you gave me and received confirmation that the user I configured was allowed to login.  Unfortunately, the resolution of this issue brought me back to the issue we first had with this user's login.

The Record source 'Swithboard Items' specified on this form or report does not exist.

We originally received this error when this user was a member of the Domain Admin group.
When we removed him from that group, we began to recieve the error message mentioned in my first post.

After performing the task of making sure the user could login to the database I thought about our issue.  I remembered that it cannot be a permission issue with his MS SQL account because this user can login to the database as long as his is not the one logged in to the workstation (any workstation).  His Windows login account is the culprit.

Have you ever had a login account that blocked access to a database?

Thank you for your time.  If you have any other ideas about the steps I should take next, please let me know.

Thanks again,

Mike