I have created a Linked server from SQL Server 2012 Express to a Sybase Anywhere using the ODBC driver Sql Anywhere 12.
I've made several views of single tables of the Sybase Database and I can read the data without problems.
On One of the tables I've put in the views I have also Write permissions. the view is made with the following statement:
SELECT id, descr, descr2, ... all other fields
FROM OPENQUERY([SYBASE], 'SELECT * FROM MyDb.DBA._MyTable') AS derivedtbl_1
If I execute the following statement
INSERT INTO [dbo].[myview]
([id]
,[descr]
,[descr2]
,[uuid])
VALUES
('IDValue'
, 'Description value'
, '2nd description value'
,newid())
From Sql Management studio, the record is correctly added when I'm a Sysadmin
I receive the following error when I'm not a Sysadmin or when I execute the statement from an application connected to the database through a SQL or trusted user.
OLE DB provider "MSDASQL" for linked server "SYBASE" returned message "[Sybase][ODBC Driver][SQL Anywhere]Update operation attempted on non-updatable query".
I presume the problem is to grant the correct permissions to the user executing the statement or else it would not work also from the SSMS query. I've tried to give the user Insert, Delete, Update, Select permissions to a database role I've assigned to my user but the error does not change. Is there something else I need to do to give the right permissions to the user?
Do I have to give somehow permissions to the user on the linked server?
thank you in advance
regards