Can anyone tell me how Navision accesses SQL server?
I have this problem where Navision has to access beside our Navision database also another database which is linked to Navision database.
So when i try to access (in Navision) tables from the other DB i get this error message:Server user 'prosen' is not a valid user in database 'otherDB'. This message is displayed until i grant user System Administrator role on SQL server.
If anyone has any ideas how to solve this, please help.
We are trying to do just this, i.e. create a view through which a Navision user can access data in a different DB. This all works as long as he is db_owner in the Navision DB, but when he is not it fails with a permissions error. We cannot allow all our users to be db_owner (170 people who would alll be able to back up, delete etc. as much as they would like once they create an ODBC connection would cause me sleepless nights) and have tried your suggestion of a linked server, but it appears you cannot create a linked server pointing to itself in SQL Server 2005, we get the message ¨You cannot create a local SQL Server as a linked server¨. The DB we are trying to access is on the same server box. Any ideas what we can do now or are doing wrong?
Hmm, got it working now by using the IP address to create the linked server, now it is now longer complaining about creating a linked server to itself. Created new user with appropriate permissions, supplied the linked server with the new credentials, created a view inside the Navision database to look at the table in the linked server, assigned the application role the appropriate permissions on the view and we are in business. Not sure about the performance impact of all of this, but it does not work without having to give the user db_owner access to the Navision database. Thanks to Robert for the hint on using a linked server.
You really should think twice about giving users db_owner access to your Navision database though, it means any user with a modicum of SQL knowledge can do anything to your data at the SQL level, including backing it up, dropping the DB, inserting incorrect data etc.
Grant the Guest account access rights for the other database. When using application roles access to remote objects is thru the Guest account.