Maintain a SQL user on a Dynamic NAV 2009


I have a problem that is driving me insane; or close to anyway.

I have a setup with an SQL server running a Dynamic NAV 2009 (they are using classic client). I have made a model in Excel and I need to read some information directly in the SQL database and also write in a couple of new Navision tables, that I have created; so no writing to ledger tables or anything.

The problem is, if I create a SQL user, it is removed the next time I synchronize the users from Navision. So we created a domain user in Navision and this user was given the correct read and write access in SSMS afterwards. I am unable to use this domain user as a way of access the SQL database, because Excel are only able to use the current windows login; not an alternative.

As I see it, the only way is to create a SQL user that has the required access and find a way for it not to be removed every time I synchronize. Or is there another way I just do not know about?

Hope you can help and have a good weekend.

Yours sincerely
Henrik Larsen

Add the domain account, that you are using to access Excel, to NAV under “WIndows Logins”.

Or just put that “SQL Login” to NAV’s “Database Logins” (you even don’t need to assign roles). NAV just needs to “know” about this login, the the Synchronization won’t delete the USer from the db …

@Joerg, do I understand it correct, if I say that I can do this:

  1. Create a databaseuser
  2. Synchronize to SQL from Navision
  3. Add the read (and write) to the tables I need the user to have access to - this is done from SSMS
  4. The next synchronization should not delete the access I have setup in SSMS