Make Your Security Access-Friendly

Try this trick to give user the permission they need to update SQL Server records via Microsoft Access.

• By Mike Gunderloy
• 02/01/2002

Savvy DBAs know that keeping users out of your tables is one of the key steps towards keeping your data secure. Instead, you should assign data permissions to users via the views in your database. For example, you can give a particular user permissions to update a view, and they'll be able to change the data exposed by that view even without any permissions on the underlying tables. This gives you the flexibility to customize data access permissions or even change them on the fly by simply altering views, while avoiding giving users blanket permissions on tables. With views it's easy to enforce row- or column-level security in special situations.

But if you're using Access 2000 or Access 2002 data projects (ADPs) as a front-end to your SQL Server database, you'll find that there's a catch to this scheme. It turns out that Access won't make proper use of view-based permissions. Using an Access 2002 ADP, a SQL Server 2000 database, integrated Windows security, and a user with update permissions on a view but not on the underlying table, you'll find that the user can update the data just fine through SQL Query Analyzer, but not if they're using Access!

Don't despair. Here's a little-known trick to make this work. What's happening is that Access uses ADO to get to SQL Server, and ADO (and the underlying OLE DB layer) tries to optimize updates on views by sending the updates directly back via the underlying tables. This is fine (and fast) in most cases, but it totally defeats the security setup I've described. To make things work the right way, make a property setting that tells OLE DB to use the view itself to do the updates.

The trick: Add the WITH VIEW_METADATA clause to your view definition. You might define a view in the pubs database this way:

CREATE VIEW dbo.vwAllAuthors
WITH VIEW_METADATA
AS
SELECT *
FROM dbo.authors

It's the WITH VIEW_METADATA clause that does the trick.

I tested this fix in both Access 2000 and Access 2002 ADPs, and in both cases I can update data via a view even without permission on the underlying tables after making this change (provided, of course, that I've got permission on the view). One caution, though: The WITH VIEW_METADATA clause is new to SQL Server 2000—you won't be able to use this technique with older versions of SQL Server.