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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.