I ran it as the other user. I ran it as myself (both times using execute as user). Got the same error message. Running it WITHOUT execute as user, it runs fine for me.
In the meantime I just tried using 'execute as login'. I seem to be getting somewhere using that, as I am now getting:
'Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'Program', database 'mydbname', schema 'dbo'.
However, users have no permissions against any tables - only execute permissions via the role.
I get data from linked server using OPENQUERY. The returned data links to local SQL Server table & is then inserted into SQL Server table. (@sqlqry has been put together in stmts prior to this running):
SET @Mysql = N'
INSERT INTO MySQLServerdb.dbo.Mytable
(
columns
)
SELECT
listed columns
FROM OPENQUERY(linked_server_Name,' + @sqlqry + ') AS s
INNER JOIN Program AS p ON s.program_id = p.ProgramID
WHERE s.Col_ID NOT IN (SELECT ColID FROM MyTable)'
exec @Mysql