User cannot, admin can, link tables over ODBC from MSSqlserver?

Hello,
I have a sqlserver2008 database on server2003, which only Admin can login on it. I have create a small report program with MSaccess. I have linked tables with ODBC from sqlserver. Then I have put Access file in sharing folder that other users could run/get report. But, of course first of all I was need to open ODBC also on client computers. But, here it stops. Because ODBC shows only System Tables from SqlServer, and not actual tables with data.
As Admin, I can linked, and get/see all tables, but users not. At this point I know it should something with permissions. With Sql Management Studio I open sql server without authentification.

Any idea? It would be really helpfull if someone could help me, advise,...

Thank you in advance for help.
dejandejanovicAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tigin44Commented:
SQL server uses two phase authentitacation. First login authentication checking if the user login is defined to access the sql server. Second user authentication checking if any login granted access to the database. In your case its highly probable that the users who are cant reach the database dont granted with enough privilage. You should define the logins and also add the users related to that logins to the database in order to connect.
Another way can be using the sql server authentication. If you use this authentication type you should deine an sql login and relted user account and then modify your report and odbc to use this dedicated account.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
arnoldCommented:
As tigin44 points out, it seems that you are relying on SQL server to enforce access.
Often, people would have a users tables as part the database such that the application will use an SQL login to gain access to the sql server and database and then prompt the user for a login. Once that matches the database username/password the user will be permitted to do stuff based on parameters within the database.

In your case you need to add the windows\login of the user into the sql server security section.  You then have to set what permissions this user has with regards to the database i.e. datareader, datawriter, etc.

The former is more secure as the only way the user can access the database is through the use of the application, this way the user can directly access the database if they have access to the sql server management studio (express).  
dejandejanovicAuthor Commented:
Thanks for explanation. I will setup sql authentification...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.