Permission problem with linked table

I have encountered a permission problem with a table using the LinkedObject property. The implementation is NAV4.0SP1 on SQL2005 with Windows Authenticated users.

The table view within the NAV d/b points to an external SQL d/b on the same SQL Server. Through NAV security, I have assigned “read” permission to the linked table and synchronized, and at the SQL level, that user has been granted select and execute rights to both the view (within the NAV d/b) and to the underlying tables (other SQL d/b) which the view points to.

From within NAV, when the user tries to access the linked table, NAV returns the message, “You do not have permission to read the… table”. However, accessing the view externally (e.g. in Excel via ODBC) the user is able to access the view.

I suspect that it may be associated to the application roles which are used by NAV ($ndo$ar$000…), though this is the limit of my knowledge on SQL security.

Can anyone help, please!

If you grant rights for this view to the roles public and guest in SQL Server, does it work then?

Thanks for your suggestion, Jorg. In answer to your question - yes, it does.

Granting the [public] role select rights to the underlying table does allow access for the intended user, but it opens up access too wide. We only want to allow authorized users select access due to the sensitivity of the information.

When using application roles all other user rights are disabled. Access to resources not covered by the applicaton role is granted from the Guest account. This applies when setting up link tables (and views) from Navision into another SQL database. You must grant the Guest account the required access.

Using the [guest] account seemed to have reolved my issue. Thank you both for your replies.