The latest weeks I have received a couple of questions from various Dynamics NAV consultants regarding strange SQL errors Dynamics NAV was throwing.
A couple of examples:
- Table XXXXX coes not exist.
- Cannot find the user ‘$ndo$…….’, because it does not exist or you dont have permission.
- You do not have permission to execute table XXXXX.
- You do not have permission to read table XXXXX.
But actually the problem here was not SQL server, it was due to something else.
Usually when I get a call with this kind of error I ask some questions to see if it’s not a ‘real’ security issue. The user might not have the appropriate security role(s) so it could be a ‘normal’ error.
Then, when there’s no obvious reason for the error, I ask about the security model: “Are you using the Standard or Enhanced security model?”
Most of the time the answer is: “Euh?” or “I think it’s Enhanced, let me check… yes it’s Enhanced.”
Then I ask, why. “Is there any reason it is set to Enhanced?” or “Do you know someone who knows the reason it was set to Enhanced?”
If there’s no answer to this question then I propose to aks the same question to the Dynamics NAV DBA or the person in charge of Dynamics NAV security or the person in charge of AD.
Until now, no one has ever been able to provide a good reason why it was set to Enhanced, except of course: “Enhanced seems better then Standard” or “It was like that when the database was created and we never changed it.” or “Isn’t Standard less secure then Advanced?“
Actually, in my opinion, the Standard option is more enhanced then the Enhanced option 🙂
Have a look at the differences between the two listed here:
Now tell me, why would you choose Enhanced over Standard?
Probably the reason that Enhanced is used so much is because it’s the default option when creating a Dynamics NAV database (since version 4sp3).
My suggestion is: If you don’t know or can not give any good reason to choose Enhanced, then try the Standard security model.
So now you might think, why was the Enhanced option added and what’s its purpose? Well, I have been looking for a good explanation for years and finally found it here:
(scroll down a little, the good part is in the middle)
This is really a great article. IMHO have never seen or heard a better explanation about this, and that was already posted in 2008.
- If you are an end user company then contact your Microsoft Partner, and ask their opinion and support, do not do this without consent.
- If you don’t have experience and/or knowledge about Dynamics NAV and/or SQL Server security or feel uncertain, ask an expert for advice. Don’t do this yourself.
- Do not change the security model before first testing the procedure on a TEST environment. Make sure you know what to do, have the appropriate security rights and stored procedures at hand.
- Don’t do it during business hours. To be able to change this setting the database needs to go into “Single User mode”. After the change, do not forget to remove the “Single User mode”.