Cant connect to sql server table

[tagteam]

I have a linked table in my access database that links to sql server. The database is on terminal server.

I created the table as my user so I can open the database and access the linked table. However, my other users cant. In SQL server management studio I have added them as a user and they have connect access. However, when I open the database it opens it as read only and i get the following error when I try to connect to the linked table.

Connection failed:
SQL State: '28000'
SQL Server Error: 18456
Microsoft Sql Server Native Client 11 Sql Server login failed for user 'Domain\username'

I have made sure that user is added to that database
I can access it it as my user from the same machine that the other user is failing from.



Any ideas?

[ranman256]

is the ODBC DSN set?

Driver? I have users that have a different SQL driver than others. Some use {SQL server},
some use :"Microsoft Sql Server Native Client 11"

try switching it.

[Gicu]

In SSMS give them db_datareader and db_datawriter roles.

Cheers,

[tagteam]

The user has reader and writer roles
I also have the sql native client, i can try switching to another driver.

[Gicu]

You mentioned terminal server, are you sharing the same front-end or each user has their own (preferred setup)? Are the folder permissions the same (you need read/write permissions for the folder that contains the front-end. Are the trusted locations set for the other user (they are user specific).

Cheers,

[tagteam]

An update to this thread. I checked permissions and swapped drivers etc and nothing worked. However, I did finally figure it out.

Here is what I had to do:
Create a system DSN - I was trying to use user DSN so I could limit to that user but it appears that a program started on terminal server is not quite like a logged in user.
Create a user in the SQL Server security section (not the actual database) and assign/limit rights to the database there

Previously, when i have done this succesfully in the past I just needed to add the user to that particular database, not the overall sql server. At that time the database was on the same server as the application so that may have been the difference.

[tagteam]

Further update, just to be clear. I had added the users under the security tab in the first database, but somehow missed that. Long day???

So basically my question was a bad one, just make sure you have created the user under the SQL server Security section and then add them to the database using the User Mapping