Updating views with VBA-ADO in SQL Server requires UPDATE privileges on underlying table (MS Access-CodePudding

I have a problem updating views with ADO in MS Access.

I created views for my tables, they contain columns and rows that are eligable to be changed. For example, the table tbl_tablename will have the view: vw_tablename_editable with some WHERE clauses to determine wether the the rows should be editable or not.

CREATE VIEW vw_tablename_editable 
AS 
    SELECT tablename_id, tablename_fieldtoupdate 
    FROM dbo.tbl_tablename 
    WHERE table_criteriafield > 1000 -- 1000 being some criteria

The permissions work: when I run UPDATE queries on the view as a user with only UPDATE permissions on the view, the information is correctly updated. Also when using DAO linked tables in Access, I can view and modify the data.

The problem is when I am using ADO and try to update the views, both using the Bound controls and manual updating in VBA.

Dim rs as ADODB.Recordset
Set rs = New ADODB.Recordset
        
    With rs
        Set .ActiveConnection = connectionstr            
        .source = "SELECT tablename_id, tablename_fieldtoupdate FROM vw_tablename_editable " & _ 
        "WHERE tablename_id = " & id_ofrecordtobeupdated
        .cursortype = adOpenStatic
        .cursorlocation = adUseClient
        .locktype = adLockOptimistic
       .Open
   End With 

rs!tablename_fieldtoupdate = "new value"

rs.update

The error message I am getting is:

The UPDATE permission was denied on the object 'tbl_tablename', database 'mydatabase', schema 'dbo'

It seems to be related with this question, however, the SELECT statements in ADO just fine without giving the user access to the underlying tables.

The problem is solved when I give the users direct UPDATE access to the tables or the DBO schema, but I would like to avoid this. When logged in as DBO updating works both with the MS Access bound controls and the manual VBA way.

I also tried to make an indexed view, but this also does not seem to make a difference.

Edit

By suggestion of user @wqw inserting WITH VIEW_METADATA, it now works perfectly!

CREATE VIEW vw_tablename_editable 
WITH VIEW_METADATA
AS 
    SELECT tablename_id, tablename_fieldtoupdate 
    FROM dbo.tbl_tablename 
    WHERE table_criteriafield > 1000 -- 1000 being some criteria

CodePudding user response：

You just need to create your views using WITH VIEW_METADATA option which is generally harmless for anything T-SQL related (e.g. quries or stored procs) but instructs OLEDB/ADO (and other data-access connectors like ODBC) to update through view’s columns and not the (several) base tables.

CodePudding user response：

Ownership chains will suppress permissions checks on tables when any DML is executed on a view owned by the same user. Apparently ADO is discovering the underlying table and accessing it directly.