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:
在具有SQL后端的Access项目(ADP)中,Access能够更新从绑定到表单的视图返回的记录所需的最小权限是多少,但是阻止了对基础表的直接SELECT?例如:
Table: Table1 Columns: ID, Column1, Column2, Column3, Column4
表:表1列: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.
作为数据库所有者或角色db_owner(或角色db_datareader,db_datawriter),可以在绑定到视图时通过Access更新记录。但是,如果用户不是这些用户的成员,则该记录不能通过Access更新。我将SELECT,UPDATE授予View1并拒绝SELECT到Table1并允许UPDATE到Table1(如果视图上的UPDATE不起作用)。我还拒绝UPDATE到Column1,因为我不希望用户更新该列。
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.
如果我使用这些权限设置连接到SQL数据库(使用SQL Server Management Studio),我可以更新视图中的记录,但在Access中我不能。为什么是这样?我不知道Access正在发出什么查询来更新记录以确定它失败的原因。
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.
仍然没有找到一种方法来找出Access没有SELECT需要UPDATE的权限.SQL Profiler没有显示'recordset not updatable'消息的任何明显原因。
1 个解决方案
#1
Not sure it's possible for Access to update something it can't select.
不确定Access是否可以更新无法选择的内容。
You can attach Sql Profiler to the server to see which queries Access is running.
您可以将Sql Profiler附加到服务器以查看正在运行Access的查询。
#1
Not sure it's possible for Access to update something it can't select.
不确定Access是否可以更新无法选择的内容。
You can attach Sql Profiler to the server to see which queries Access is running.
您可以将Sql Profiler附加到服务器以查看正在运行Access的查询。