mibuso.com

Working on SQL2005/NAV5 [Security Model: Enhanced]

I have noticed strange issue.
If we create a Windows Login in Navision, and synchronize all security, username in sql will be created (with its own schema attached to it).

Let's say we do not give super role to the new user, just some roles to read customer table, etc. After synchronization, when new users logs into navision he is getting error message:





And let's say we try to open Budgets with same user (for which we do not have permissions). We will get error (instead of - you do not have to read... ):



Furthermore, if I would go to SQL and there would add db_owner role, I would get error message (which is actually what i expect to get):



Had anyone else noticed similar issues? Any ideas and discussions would be helpful.

Every user needs the ALL role in order to login. Have you given that to the user?

Yes, I did.

What I am trying to say is, that instead of normal Navision error that you do not have permissions, I am actually getting SQL error (which is strange).

........

Let's say, if you create a login in SQL first, it will have default schema defined as DBO by default.

If user is created at the moment you synchronize all security, the user will have a default schema named by the user itself.

in 2005, the security is stricter, in that if you don't have the select permission, you won't be able to actually see the table.


It is wierd that you are seeing SQL Errors instead of Navision. My guess synchronization didn't finish or is screwed up.
Personally I've given up on enhanced security model.

Security was synchronized with user 'sa'. I did not get any error messages while synchronizing. And keeping in mind, that new user was actually created when synchronizing, I am assuming that synchronization was successful.

In SQL2005 probably public role is not good enough with NAV Enhanced security model.

As for synchronization being screwed up - problem is that I was able to replicate same issue on two different machines.

[Topic moved from Navision forum to SQL General forum]

Why not use Standard Security model? It makes life a lot easier.

It wouldn't be so interesting then...

To make it even more interesting, you should open a help ticket with MS.

Seriously, why aren't you using standard security? If you need any external apps accessing the NAV database, create users for them manually directly in SQL Server.

Possible (and no so possible) solutions to this problem:
1. Add db_owner role to the user (yeah, right - not possible)
2. In the database, find user. Check it's schema. Go find that schema. Change default owner of the schema to dbo (works like a charm, just if there are a lot of users, then there are a lot of clicking in order to solve this issue). And in the future, you will firstly need to create a user in SQL before adding that user into Navision (even if you are creating Windows logins).
3. Change Security model to Standard (which usually everyone goes for). And by everyone I mean myself included.

Thanks for everyone's input, anyways.

No it's not weird....this is the difference between enhanced and standard security...
Enhanced: SQL wil be in charge of permission to a table. For each user a role will be created and in that role all tables will be present with the correct permissions. Nav clients sends selects etc.. directly to sql who will allow or dissallow according to the role settings.

Standard: One role exists in sql, and that role is used by every users and in that role all permissions on all tables are allowed...

The nav client will check prior to giving the select statement (or alter or delete or whatever) to sql if the user has rights into this table , and if not navision client will block and give the error message....

Stay with the suggestion of everyone here, use always standard security....never enhanced (not seen one example of a client who really needs enhanced security, but seen a lot of problems with it....)

Sometime ago I asked MS Support to give me an example of a customer where Enhanced Security would be the better choice. They were unable to give me an example. That being said - Use Standard.

From what I understand itis that when Navision was purchased by Microsoft, some MS team did an evaluation of the Navision security system, and found that because the security was not enforced directly on SQL Server, that the system was not secure (or some other words that meant that it did not comply with Microsoft standards), and they forced the NAV team to change the security system to push permissions down into the user level on SQL Server.

I don't remember exactly which version it was, but that is when the 'enhanced' security option was the only option for SQL Server. When that caused problems in just about every single NAV implementation they decided to give back the old security system, and give NAV users the option to select the basic system, and thereby giving the users the responsibility of selecting a "less secure" security system.

My question to them was not "what it was". I was quite clear on the what. My question to them was "why would one use it". NAV security has always been enforced directly on SQL. It uses SQL Application roles which are a SQL feature and not a NAV feature.

The use of roles on the standard system, is only there to be able to access the database via a navision client and not directly via a sql login...

So once you have access the security per table is taken over by navision...

I think it's only how you put it, but imho when using standard, it's the navision client that enforces the real security....