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.
I don't fully understand the issue that you hit, but here's an example of how you can separate tables and views in different schemas and grant permission on the views schema, so that a user can select from the views, but not from the tables:
-- create a test database and a test login&user
--
create database test
use test
create login alice with password = 'Vl&cptn1cf0'
create user alice
-- create separate schemas for tables and views
--
create schema sch_tables
create schema sch_views
-- create a table and a view that selects from it
-- select will work due to ownership chaining
-- because both schemas are owned by dbo
--
create table sch_tables.t (answer int)
insert into sch_tables.t values (42)
create view sch_views.v as select * from sch_tables.t
-- allow alice to select from the schema sch_views
--
grant select on schema::sch_views to alice
-- now test that alice can select from view but not from table
--
execute as login = 'alice'
-- this select will work
--
select * from sch_views.v
-- this select will fail
--
select * from sch_tables.t
-- revert impersonation of alice
--
revert
-- cleanup
--
use master
drop database test
drop login alice
Thanks
Laurentiu
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.
|||How does Access connect to SQL Server?
Are you granting SELECT on the schema to the same user that you are adding to the db_datareader role?
Thanks
Laurentiu
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.
|||Can you post a sample script that shows what you did?
What do you mean by not being able to see the views? Can you post the error message that you received?
Also, where do the views show up with an invalid schema? Have you checked the schema name using the catalogs using a query such as the following:
select schema_name(schema_id) from sys.objects where name = 'view_name'
Thanks
Laurentiu
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?
No comments:
Post a Comment