SQL Permission Problem Navision 5.0

borjagh

Hello,
After being investigating this problem, I cannot find a solution for myself.
When I try to use the new Send to Microsoft Office Word function in NAV 5.0, it goes OK if the user is dbowner, but if the user is public in SQL, I obtain a SELECT denied error for table 2000000067 "User Default Style Sheet". If I try to run this table from the object designer I get the same error.
User has SUPER role, and I have synchroniced permissions with SQL. I 've no problem to run other near tables (Style Sheet, Send-To Program). I don't understand what's happening ](*,)
Does anybody knows the solution??

Thank you,

Borja Grosso
Sevilla

kine

And license permissions?

borjagh

kine wrote:

And license permissions?

I've checked it with my last partner license, upgraded to version 5.0, and have the problem.

It goes well with the same license if I use Propietary database, or SQL Server but the user is dbowner.

kriki

[Topic moved from Navision forum to SQL General forum]

kine

Which security model - Extended or Standard?

Nobody

Knowing from past bugs that the developers only test with DB_Owner or SA logins you may have found a bug. My first guess is that the table does not exist in the default schema so the sync can not assign permissions to, and app roles created by the sync are "attached" to the default schema. Just a guess

borjagh

Nobody wrote:

Knowing from past bugs that the developers only test with DB_Owner or SA logins you may have found a bug. My first guess is that the table does not exist in the default schema so the sync can not assign permissions to, and app roles created by the sync are "attached" to the default schema. Just a guess

I also think it is a bug. I've just made the test with the CRONUS database and obtain the same error.

borjagh

kine wrote:

Which security model - Extended or Standard?

I think I use Extended Security Model, but not sure how to check this.

All my guesses are that NAV 5.0 with SQL Server 2000 permits simple and complex security model like in 4.0.

I created a Windows group for navision_users and added this group to SQL logins. I add to this group all the users in the Active Directory I want to have access to Navision.

Another thing I can tell you, altough I think it has no relation with the problem:
Before creating the SQL database from Navision I copied to the SQL BINN directory xp_ndo.dll. I don't know why, but I had to manually create extended procedure xp_ndo_enumusergroups pointing to xp_ndo.dll, and granted public execution permission. The extended procedure xp_ndo_usersids was created automatically.

borjagh

Hello, I've been searching for a fix but couldn't find any yet. I tried to fix codeunit 403 to get around the problem, but it has no reference to table "User default style sheet" and although I turn on the debugger, SQL permission pops up immediately without giving tracks me of where to patch the error .

kine

1) Be sure that you have up-to-date license file including the permissions for this table (check virtual table License Permission for more info).
2) Try to switch to simple security model (File - Database - Alter - last tab...)

sailor

I had the same problem. Changing the Security Model from Enhanced to Standard solved the problem.

borjagh

Microsoft published in Partnerguide that they have the solution: it solves many other problems too.

Knowledge base article number 943858 . (Platform update rollup)

Don't know why they require you to open a support incidence to get de update, and they don't make it available for partners in the partnersource directly.

theo.s

I have recently moved a Customer running NAV 5 SP1 from NAV Database Server to SQL Server, and am experiencing some interesting SQL problems:
1. I was amased at how many new tables showed up as not having permissions, considering everyone has been working fine for 18 months before the change.
2. The non-super users are not able to Send To Excel (or MS Word), getting a Read Permission problem on Table 2000000067 "User Default Style Sheet". Adding this table to the ALL Role, and even synchronising All Users does not cure the problem. ](*,)


Is this a known problem in NAV 5 SP1, and how can I cure this problem?

PS: I have had other cases where I add a Table permission to ALL. and it did not do the trick. I had to Remove a lesser permission to the same table from another Role (used by the same user) to get it to work. I am starting to suspect that the SQL C/SIDE Client might not be using the greater of the permissions..

stryk

Well, once in a while it seems to happen that during the Sysnchronization process - especially with "Enhanced" Security Model - the effective user-rights on the SQL objects get lost ...

In most cases this could be fixed by assigning the required permissions directly in "SQL Server Management Studio"; for example:

USE [My_NAV_DB]
GO
GRANT ALL ON [User Default Style Sheet] TO PUBLIC
GO

Instead of "ALL" you could also specify SELECT, UPDATE etc..

Does this help?
Regards,
Jörg

theo.s

Thanks for your reply Jörg,
Obviously this defeats the pupose of the NAV security amangement somewhat, but at least I know that there are some anomalies still in this side of Permissions in SQL, that will hopefully be resolved at some stage (soon?).

I have another Issue, this time to do with using (Domain) Security Groups in NAV. I found I could not use them at all (Tried on three different installations - they all do exactly the same, using both NAV V5 SP1 and NAV 2009).

If I add a NAV Windows Login, that is a Security Group, with some Role Assigned, I can no longer Synchronise the Permissions from NAV. I get the SQL Error ...Cannot find the user '$ndo$ar$<very_long_number>', because it does not exists, or you do not have permission. This happens on the T_SQL GRANT statement.

I am trying this as a user with sysadmin and am also the db_owner.

Much as I would prefer to use them, I have given up trying to user Groups for now, assigning Roles directly to the Windows Users.

Also a know issue, or am I consistently not uderstanding something here, do you think?

PS: What is the best channel to give this type of feedback, hopefully for the developers to try and resolve?

Kind regards,
Theo

stryk

Well, I'm not sure what causes the error you describe ... maybe a bug in your NAV version? :bug:
I recommend to look into the NAV Security Models: obviously you are using "Enhanced" security, but I would prefer "Standard". Maybe changing the SM would also solve this error?
please look into the "Installation & System Management (SQL)" PDF to compare both SM.

Regards,
Jörg

simes

I also have the problem with synchronising logins stopping working once windows groups are added in NAV when using the Enhanced Security Model - or rather once the group added has been assigned a role in Navision.

Has happened when setting up a test server with two different NAV databases taken from Live which have been configured by different MS partners and with very different pedigrees. Works OK if switched to Standard Security - unfortunately this is not an option for the moment.

Groups are Domain Local, members are in local domain, members are also added as windows logins in Navision and I'm doing all this as sysadmin on SQL server. Synchronise All fails and so does Synchronise Single Login for each user that is a member of a group with a role defined. The $ndo$ar$<very long number> not found message equates to the Application Role for that particular user (or first one enumerated if Sync ALL) with the long hex number equating to the user's SID. It can't find it because it gets removed at some point (can't remember if it this is when the group is added, role added to group or the first sync is attempted in this state).

Current version is NAV 2009 RTM with SQL 2005 SP3 so obvious question is whether this problem goes away with a SP1 or a further rollup?

Does anyone know?