Access Cookbook

Use Views to Update Data in an ADP When Users Don’t Have Permissions on Tables

Problem

You have secured your SQL Server 2000 database and removed all permissions for users to directly interact with tables. You have created views and granted users permissions to update data through the views instead. However, users normally are not allowed to update data through views when they don’t have access to the underlying tables. How can you allow them to update tables through views?

Solution

Whenever you secure your database in SQL Server, you have the option of denying permissions on tables and granting permissions for users to work with the data only through secondary objects such as views, stored procedures, or user-defined inline functions. As long as both the underlying table and the secondary object have the same owner, SQL Server does not check permissions on the underlying table and simply executes the action based on user permissions granted on the secondary object. For example, you can deny permissions for users to select data from a table, and then create a view that selects data from the table. Then grant users permissions to use the view, as shown in Figure 14-19.

Figure 14-19. Using SQL Server Enterprise Manager to grant permissions for views

You can then update data through the view instead of the table, as long as you have permissions granted on the view. This allows you ...