Our solution provider implemented links to an external database with a view to a linked object.
This works really well until I have to let some users loose on the data at which point I get an error message saying: 916,“08004” [ODBC Sql server Driver][Sql server]server user blah is not a valid user in [the other database]. Fine I thought, I’ll give them permission on the tables in the other database directly.
However, I’ve given the users all permissions in the tables in the remote database, and also directly on the views in the navision database. I can see it, but I’m an administrator & presumably using builtin\admin. Giving them super in permissions also does not work (luckliy).
Those users must have access to the other SQL database, so you need to modify the SQL user, not just in Navision. Just doubleclick the user in Enterprise Manager and add the other database to their profile, you may have to add db_owner. That worked for me.
I gave the user concerned all the rights I could think of but to no avail. I can use MS Query via excel & get the data, but not from the Navision form. I’m still mystified. Perhaps it’s a read permission overriding the read write permission required in the chain of ownership. I’ll keep this post, er posted…
If it is not a confidential information, you can give read permissions to guest user on both databases on tables/views you want or make it a member of db_datareader group.