Views and Underlying tables w/ Access Data Projects

[S22]

I don't know if there is a fundamental problem with what I am trying to do, or am I just having problems setting it up correctly:

I have a SQL server multi-user database. I want my users to connect to this databsase via Access 2000 Data Project. No problem there. The database consists of one main table and several views (based on the office branch that the user works from). For example there is a Chicago view, an Atlanta view, etc. that all extract different records from the same underlying table. I need my users to have FULL ACCESS (select, update, delete) to their respective VIEWS, but they cannot have access to the underlying table. I've tried several configurations and I'm beginning to think that this may not be possible... is that the case?

If it is not possible to grant access to views but not the underlying table, then what are my other options? The objective is to have a multi-user table that each user "owns a piece of" without being able to see the tables or records belonging to their peers. Do I need to setup a table for every office, and somehow link those tables into one main table? How would I avoid duplicate records being entered into the separate tables? Any help would be GREATLY appreciated, as this problem has had me stumped for weeks.

 

[jhall156]

Have you tried this?: in the enterprise manager under your database/views, right click your view, select properties and then click the permissions button in the upper right. Give permission here and not in the table.

 

[tlbroadbent]

This is a very basic security concept and is easily achievable with Enterprise Manager as described by JHall156 or with T-SQL from Query Analyzer.

Grant Select,Update,Insert,Delete
On AtlantaView
To AtlantaUser -- user or group name(s)

Revoke Select,Update,Insert,Delete
On TableName
From AtlantaUser, Public
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[S22]

Thank you all for your help. Unfortunately, I still have had no success. When I try this code this is the error I get when trying to update a record in the view:

SELECT Permission denied on object 'testtable', database 'testdb', owner 'dbo'

If I grant SELECT permission to the user for the underlying table 'testtable', I get the following error:

UPDATE Permission denied on object 'testtable', database 'testdb', owner 'dbo'

The view definitions are very simple select statements based on the value in the "office" column. i.e. Select * from table where office = 'atlanta'

I am using Access 2000 data project to connect to the database.

Any other ideas? Anyone?

 

[tlbroadbent]

It appears that you need to use the "WITH VIEW_METADATA" clause when creating the SQL view for use in ADP. An example follows.

CREATE VIEW dbo.vAtlanta
WITH VIEW_METADATA AS
SELECT * FROM dbo.TableName
WHERE Office='Atlanta'

Review the following articles for more info.

http://www.databaseadvisors.com/newsletters/0111ViewMetadata.cfm

http://support.microsoft.com/support/kb/articles/Q253/6/73.ASP

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[S22]

Thanks again. The first article describes EXACTLY what I need to accomplish. However, there is only one problem: I am running SQL Server 7.0 and the VIEW_METADATA option only exists in SQL Server 2000. I also tried to do set it up through Access as described in the article, but I didn't see the "View Tab" that he describes. Is there any way to do this w/ SQL Server 7 or will I need to upgrade to make this happen?

 

[tlbroadbent]

I don't know what can be done in SQL 7. The only suggestion I have is to consider updating the tables via stored procedures rather than views. You should be able to control access within SQL Server if you do that. I realize that could be a big change but I've not had any experience with the problem you've encountered so can't offer much help. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[RickCole]

If you just had:

Grant Select,Update,Insert,Delete
On AtlantaView
To AtlantaUser -- user or group name(s)

And no permissions at all on the table does that not achieve what you want??

Rick.