Access project connecting to SQL 2005

Most of our users are using MS Access project to work with our data in SQL Server 2005. The problem we have is that in order for them to access the tables they need to be given db_datareader and db_datawriter permission on the database. Our goal is not to give access to the tables themselves but to views. Therefore, we've created several views and placed them into a particular schema. Now we want to give access to that schema, but when we assign select, delete, insert, and update to that schema user in ms access project aren't able to view these tables. When we give them db_datareader permission they are able to see the views but in parentencies does not show the correct schema; therefore, when you try to open that view it errors out saying it doesn't exist. Also since they now have db_datareader they are also able to access the other view and tables in the database.

What we are looking to do is give our Access Project users the permission to link to SQL Server 2005 views by schema only.

Thanks for the reply but we have done what you are suggesting and it works great when you access through Management Studio. Our users are using MS Access projects. Maybe I am missing a step but if we don't give db_datareader they won't even see these views and when they do have db_datareader all objects that do not have the "dbo" schema doesn't seem to work. It will show another schema entirely. It looks like a login name and not necessarily yours.

We're talking about installing Access 2007 but I don't know when. Maybe it will work better. I did test the beta version and it appears to work but the new Access environment is so much different and only being a casual user of Access I don't know if there are any other problems.

Yes, after I moved the views into the schema I then granted the user select, update, insert, and delete permissions on that schema. I am connecting with ADO from MS Access (file|connection). I am not able to see these views or any other views unless i grant db_datareader. Even when I do give db_datareader though you only able to access (open) views with the dbo schema. The other views with different schemas show up but with an invalid schema name which doesn't exist.

Therefore, for now I have to everyone db_datareader so they can atleast link to these views with MS Access. They won't be able to update, insert, or delete unless they also have the other permissions.

I just don't know if SQL is setting incorrectly or it is just a bug.

Apparently it is an MS Access 2002/2003 problem. MS Access 2002/2003 does not handle schemas at all. I got approval today to start rolling out MS Access 2007 so this will eliminate the problem.

Thanks for sparing your time.

I'm having a similar problem. Specifically, when I change a table's schema, in SQL Server, from dbo to anything else I can no longer see the table in my Access 2003 project. I used the "Data Link Properties" dialog available on the File->Connections... menu. The error I get in Access is "Table '<tablename>' no longer exists in the database", of course it does still exist.

If I use the "Link Tables Wizard" in Access to create a link to a table in SQL Server with an other-than-dbo schema I get the error "An invalid schema or catalog was specified for the provider "SQLNCLI" for linked server "<servername>", of course it is a valid schema.

I've tried everything to try to find a blurb from Microsoft discussing this issue, but I can't find anything addressing this problem. I suspect, as the previous post says, that Access 2003 simply cannot handle other-than-dbo schemas in SQL Server.

SO, my question is how do you know that Access 2003 doesn't support other-than-dbo schemas in SQL Server Do you know of a statement from Microsoft that addresses this problem