Navision 4.00 and SQL Security

We are trying to upgrade one of our clients to Navision 4.00 with the Fund Accounting piece called Navigator from the 3.70 version. If we create a native c/side database, everything works fine. If we try to restore the database to the SQL Server version of Navision, we are running into 2 key issues issues: 1. It takes 10 minutes to synchronize the security. 2. Non-super users are getting weird errors about not having permissions to certain tables. Even if we add rights to these tables, the non-super users still get the errors. In the past, we have set SQL users up with Public access on the SQL Server side and everything worked fine. For this 4.00 installation, the errors (item 2) go away if we grant the non-super users the DBO permission in the Navision database. Has anybody else run into this or have any ideas? David Hibler Finley & Cook

Well, I got a response from the Navigator support staff. Thought I’d post it here in case anyone else ran across the problem. --------------------------------------------------------- We have run into this issue before but, unfortunately we had to take it as far as MBS themselves and the solution was to make sure all Navision users were also in the db_owner role in SQL. Speaking to MBS may be the only alternative left for you. That was the only solution last we check (less than 1 month ago) If we run across any new information we’ll pass it along. VISIONPAY Services

David, A couple of comments: 1) Navision security for the SQL platform changed big-time for v4.0 – in prior versions, there was a single application role (ndo$shadow) that contained full rights to all SQL objects created/used by the Navision client. For v4.0, a separate application role (ndo$xxxxxxx-long random string-xxx) is created for each Windows login/Database login set up in Navision. These per-user app roles are only granted the permissions reflected in the Permissions table. Consequence: Synchronize can take a very long time – I’ve seen indications of 30 mins to an hour… 2) Making a user a member of the dbowner fixed database role is the ‘Sledge-hammer’ method to step around the new app roles – if the user is a dbowner, then the app role is not used. But… you might as well just remove all Navision security – any user can just start up Excel, Data, Import… and go to town on every Navision table… 3) Because of the per-user app roles, you must run Synchronize after any change to Navision Security, so the app roles get re-built correctly. We didn’t have to do this before, and forgetting to do this now is usually the reason why security doesn’t seem to work correctly in v4.0.

Fritz, Thanks for the reply. At least someone else knows about these issues. I recommended to the client that we leave them at 3.70 until the issues are resolved, but they have decided to continue with upgrading to 4.00. I agree on the dbowner fix being a sledge hammer approach. However, I believe that most of the security issues relate to items in the Navigator range (37 million) having issues with SQL, so the client feels they have no choice here.

Sorry – one more comment: 4) In upgrade scenarios, there may be Permissions missing from the ‘ALL’ role. See the second post by Robert Cincaid in the following post: http://www.mbsonline.org/forum/topic.asp?TOPIC_ID=13389&SearchTerms=permission,object