Minimum permissions required for an updatable query (Access Project)

Question

In an Access Project (ADP), with a SQL backend, what are the minimum permissions required for Access to be able to update records returned from a view bound to a form, yet prevent a direct SELECT on the underlying table? For instance:

Table: Table1 Columns: ID, Column1, Column2, Column3, Column4

View: View1

SELECT ID, Column1, Column2, Column3 FROM Table1 WHERE Column1 = 'Something'

As a database owner, or in the role db_owner (or roles db_datareader, db_datawriter), the records can be updated via Access when bound to the view. However, if the user is not a member of these, the record is not updatable via Access. I granted SELECT, UPDATE to View1 and denied SELECT to Table1 and allowed UPDATE to Table1 (in case having UPDATE on a view doesn't work). I also denied UPDATE to Column1 as I don't want the users to update that column.

If I connect to the SQL Database (with SQL Server Management Studio) with these permissions set, I can update the records in the view, yet in Access I can't. Why is this? I don't know what queries Access is issuing to update a record to determined why it is failing.

Edit:

Still not figured out a way of finding out what permissions Access needs to do UPDATE without SELECT.. SQL Profiler did not show any obvious cause of the 'recordset not updatable' message.

I've never used ADPs, but one of the many complaints about them that I recall was that in certain circumstances, ADO bypasses your views (and your security settings therein) and tries to do update the underlying tables directly. I don't think the exact circumstances were the same as the ones you describe, but it might be worth looking into. — David-W-Fenton

Andomar Andomar · Accepted Answer · 2009-06-25T10:16:16

Not sure it's possible for Access to update something it can't select.

You can attach Sql Profiler to the server to see which queries Access is running.

Minimum permissions required for an updatable query (Access Project)

1 Answers