SQL Insider
Make Your Security Access-Friendly
Try this trick to give user the permission they need to update SQL Server records via Microsoft Access.
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.
About the Author
Mike Gunderloy, MCSE, MCSD, MCDBA, is a former MCP columnist and the author of numerous development books.