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.
Dean McCrae said:Things are a bit simpler if you have the following scenario:1. The table(s) to be accessed are in another database but on the same server as main database that you are connecting to,2. Its OK, security wise, that the user(s) accessing the data can be database owners (members of the db_owner database role) in the main database you are connecting to. (Need not be sysadmins).If Yes for both, then just make your user(s) a member of db_owner in the main database. ...
Granting db_owner role is NEVER a valid solution for solving end-user access permissions. This grants the user unlimited access to the database including the right to change object definitions and also other users permissions.
BTW, I tried the suggestion of using the Guest account to the external database on SQL Server 2000 and it works fine. Anybody know for sure why this behaviour is different on 2005? The linked server approach was the only one I could get to work under 2005. And yes, giving db_owner to your users is a really bad idea unless you are really small and really 100% trust you users.