MS Access 2010 Linked SQL Server Tables in Named Schema
Posted on 2012-04-09
Our MS Access 2010 database uses linked SQL Server 2008 tables which are in named schemas. Users are granted rights through a Active Directory security group, the secruity group has been granted access as: Server Roles=public; User Mappings: db_datareader, db_datawriter, public; no securables. The longest linked table name is 41 characters (HumanResources.EmployeeAppointmentHistory)
Tested users rights driectly within SSMS with out issues.
The linked tables in ms access are schema_tablename
Error Message in MS Access
[Microsoft][SQL Server Native Client 10.0][SQL Server]The INSERT permission was denied on the object “EmployeeAppointmentHistory “, database “<DatabaseName> “, schema “HumanResources “. (#229)
I remember reading somewhere about setting up Synomyns for all non-dbo schema objects, I have not tested that yet.