How do I setup a table in SQL 2008 to be read only for ODBC connection in Access 2007

I have a single table that I do not want the users to be able to update.  Only to view.   They currently view in with the use of an Access 2007 database and an ODBC connection.     I am not a SQL admin and I don't have one on Staff but I do know how to get around the SMSS.    The user is MTCNArchive and they are currently setup on the SQL Server under user mapping with db_datareader.   I setup the ODBC connection called MTCN and tested the connection and it worked fine.   In my attempt to link table with MTCN in Access and using MTCNArchive user login and password I get an error that says "The Microsoft Office Access Database engine could not find the object ''dbo_RECV'.   Make sure the object exists and that you spell its name and the path name correctly.      If I use an ODBC connection and use the sa login and password it works fine but the user can change the data, which is not fine.   Suggestions.
Who is Participating?

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

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.

You could create a view.  The view will be not-updateable unless you specify a unique identifier when you link it.  It's not the same as what you are looking for but I'm not a DBA either.

Also, keep in mind that if any table in a join is not updateable, the whole query will be not updateable so you won't be able to join to this view in any query you need to be updateable.
RayData AnalystCommented:
This is an sql user configuration issue.  Something is not set to give enough access, likely to the specific database dbo_RECV.  If you attach images of your security settings, I'm sure one of us can find the issue.
patrickmillerAuthor Commented:

you should show the connection string you created for your ODBC connection.

First, the user in your Word document is "MTCN" and not "MTCNArchive" as you said above. The database you are using is "SMSM2MData" and not "dbo_RECV". So there must be some errors in your connection string. Of course you should never give any user "sa" connection, that is an account which should strongly be reserved for administration purposes only.

If you use "db_datareader" as role you give the user complete read access to all tables, if that is OK for you? Better is to create an own role which defines the exact access rights to the object(s) you want and nothing more. You should also use a view to access the table (like Pat recommended above) and prohibit any direct access to any table. The view can be linked to the frontend like any table and you can define exactly only the needed columns and rows to expose to the user.

Moreover you should create a DSNless connection in Access, otherwise you may be asked to enter the credentials in the frontend each time you access the table/view. Here's a howto to create a DSNless connection:

A better idea would be to use Windows domain accounts to login to SQL Server, this avoids the need to send username/password over the network.



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
RayData AnalystCommented:
For safety sake, you should also check "db_denydatawriter", but maybe I'm just paranoid :-)

IF dbo_RECV is a table, then it likely should be referenced as dbo.RECV
In most cases you can skip the dbo. portion entirely as that is the default table set.
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.