I'm tyring to connect my MS Access frontend to an MSSQL database, this is a big database with many tables and many views. It is prohibited to connect as a sysadmin user.
The SQL's admin has created a user and given rights to this user (even take ownership for a specific table). But MSAccess is incapable to correctly link the tables.
In MS SQL Management Studio I can correctly see the views and tables in the software with this user but when I connect via ODBC connector in MS access while I do see the tables during the linking phase it runs into an error, and does not link the tables correctly.
I created a local MS SQL database, and with sysadmin I could easily link the tables, when I switched to a different permission set tough I ran into the same issue.
Does anyone has ran into issues like this?
The answer was that the user did not have view definition permissions. That's why it did not work.
