問題描述
可更新查詢所需的最低權限 (Access Project) (Minimum permissions required for an updatable query (Access Project))
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.
‑‑‑‑‑
參考解法
方法 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.