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.

Answer 1

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.