Getting permission to access table in sql server

[rsbutterfly16]

hi i have a question, i have the owner of a database in my sql server 2000 to be sysdba, i tried adding a user to this database by goint to EM and then going to the database , add user and just give the user reader rights to this database, then i linked this database in my access database front end; however i keep getting an error that he has no access to the sql server. I have done it this way for several databases with no errors but the owners of the databases have always been dbo, can the sysdba be the owner be the problem?

 

[ClaireHCM]

I dont think it's anything to do with the db owner as sysdba. double check the following things.

1. The connection strin information is correct, including login name, password, ip, server name, port number.

2. Make sure the login is enabled and can access the database.

Please try this out

 

[rsbutterfly16]

hi thanks for your help, in my conection string in my access database this is what i have:
ODBC;Driver=SQL Server;Server= myServerName;APP=Microsoft Office 2003; WSID= mycomputer#, Database= DatabaseName;Trusted_Conection=yes;table=tableName

I cheched my other conection strings for my other linked tables in my access database and i have the same except for the database name and they work fine.

My access database is a network database, i created the odbc conection from my desktop and then linked the tables as i did for all the other databases in my sql server in the same access database FE.


Any advice of what else can i check?

 

[ClaireHCM]

Can you have him connecting to the SQL Server database through other client tool like query analyzer or enterprise manager.

since it's using trusted connection, so login as sysadmin into the box and run sp_helplogin to see whether his NT login is there. Please also go to the specific database and type sp_helpuser to see whether his NT login is added as a user and has db_datareader permission.

 

[rsbutterfly16]

yes the user can loging to query analyzer.
I tried both sp_helplogin and sp_helpuser and yes the user comes out as a db_datareader.

this is also weird i tried using another user who has the same exact permissions in sql server and she is able to conect fine. I don't know what else to try :-(.

 

[ClaireHCM]

Could you please type out the exact error message that user is getting?

Could you also try IP address instead of server name? And also maybe try other drvier like "SQL Native Client"?

 

[rsbutterfly16]

ok let me try.. this is the exact error i get

Run-time error '3151' ODBC connection to SQL server failed

 

[rsbutterfly16]

i tried and i get the same error :
ODBC-conectionto 'sqlservername'failed and then i click on help and i get that Run-time error '3151' ODBC connection to SQL server failed


arrghhh! i am going nuts.. why do some users work and some others dont?