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.

Featured

  • Microsoft Offers Support Extensions for Exchange 2016 and 2019

    Microsoft has introduced a paid Extended Security Update (ESU) program for on-premises Exchange Server 2016 and 2019, offering a crucial safety cushion as both versions near their Oct. 14, 2025 end-of-support date.

  • An image of planes flying around a globe

    2025 Microsoft Conference Calendar: For Partners, IT Pros and Developers

    Here's your guide to all the IT training sessions, partner meet-ups and annual Microsoft conferences you won't want to miss.

  • Notebook

    Microsoft Centers AI, Security and Partner Dogfooding at MCAPS

    Microsoft's second annual MCAPS for Partners event took place Tuesday, delivering a volley of updates and directives for its partners for fiscal 2026.

  • Microsoft Layoffs: AI Is the Obvious Elephant in the Room

    As Microsoft doubles down on an $80 billion bet on AI this fiscal year, its workforce reductions are drawing scrutiny over whether AI's ascent is quietly reshaping its human capital strategy, even as official messaging avoids drawing a direct line.