Permissions on Views vs. Tables

I am using an Access ADP (version 2003) as a front-end to a SQL Server 2000
database. Based on what I have read, I should be able to not let anybody see
the tables directly, and work everything through Views and SPs. To do this,
I
grant no permissions at all on the tables, and appropriate SELECT, INSERT,
UPDATE, and DELETE permissions on the views. However, the views in Access ar
e
still coming up as "Recordset not updatabel". Only by granting permissions o
n
the tables do the views become updatable. Worse yet, if I DENY permissions
for UPDATE etc on the views but grant them on the tables, the views are stil
l
updatable.
This seems very backwards. I thought it was supposed to take the permissions
on the View, regarless of the permissions on the table (except for DENY
permissions, of course).
--
ToddYou can specify the VIEW_METADATA option on the CREATE VIEW statement so
that APIs return metadata for the view rather than the underlying tables.
For example:
CREATE VIEW dbo.MyView
WITH VIEW_METADATA
AS
SELECT MyColumn FROM dbo.MyTable
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Todd Chittenden" <ToddChittenden@.discussions.microsoft.com> wrote in
message news:187A1C5F-5CA1-4FA2-825C-859FFCF0FC20@.microsoft.com...
>I am using an Access ADP (version 2003) as a front-end to a SQL Server 2000
> database. Based on what I have read, I should be able to not let anybody
> see
> the tables directly, and work everything through Views and SPs. To do
> this, I
> grant no permissions at all on the tables, and appropriate SELECT, INSERT,
> UPDATE, and DELETE permissions on the views. However, the views in Access
> are
> still coming up as "Recordset not updatabel". Only by granting permissions
> on
> the tables do the views become updatable. Worse yet, if I DENY permissions
> for UPDATE etc on the views but grant them on the tables, the views are
> still
> updatable.
> This seems very backwards. I thought it was supposed to take the
> permissions
> on the View, regarless of the permissions on the table (except for DENY
> permissions, of course).
> --
> Todd