Navision SQL View access to non administrators


We are running SQL server 2000 on a Nav 4.0 database.

I have succesfully created a linked object to a view of another table in another database from within my Navision database. The view looks at another database on the same SQL server. I can open the table in Navision and see the table data in the other database when I’m logged in as the admin account in Navision.

Everything works fine. I’m having a bit of trouble getting it to work for a non-admin Navision account however.

I have read the posts here about SQL views and permissions but the error I’m getting is not the same as other people have experienced here but I’m sure the problem is similar. I have given the non-admin account permission to the “other” database and I even gave them db_owner. After I created the views, I synchronized the non-admins account in Navision and it synchronized fine.

When I go to open the table as the non-admin account in Navision I get a message that says “The combination of the user ID and password entered is invalid. Try again.” You would normally see that message if you typed your password in wrong when logging into Navision. There are some permissions somewhere that I have not set correctly but I can’t figure out where they could be now. I hope I have provided enough info.

Any help would be greatly appreciated.

Thanks! [:P]

You need to make sure you have granted read access rights to the view to the users of Navision this is something you do from within SQL Enterprise manager or you can do it through SQL code executed in Query Analyser - the online help for SQL has details on how to do this. This could be done by granting read on the view to public (all users) or by granting to specific users.

I am not sure but I think you may be able to get away with granting access to the application database role. As you may know when a user connects to a SQL database using the Navision application, the application uses a special database role called ndo$shadow (or something similar) this is the role that has permissions to all of the tables, etc. Navision then controls who can read what since it as an application can read everything and it can restrict what you are allowed to see. In version 4.0 they introduced a strange security model which you can turn off with a later service pack but if you only have the “Enhanced” security option then you are probably best granting read access directly to the public role.

I realise I am being vague so if you get stuck let me know and I will try to replicate and tell you how it’s done.

I got it to work. I tried adding permissions to the view to no avail. I ended up deleting the user off SQL Server completely and then adding it again. It worked after that. Thanks for the response!

If the delete and recreate option does not work, I found that you can assign the ‘Control’ privelege for the remote database object to the local database’s public role, and then as you grant permissions to the local view via Navision, the user’s can get past the authentication issue and access the view as your Navision security allows.