Excel Data Connection

ben5000

Hi Experts,

I've setup an excel sheet to connect to Nav using the "connect from SQL Server" feature (under the data tab).

I use windows authentication and have the SUPER role.

I've setup the same excel sheet on a colleague's computer (he has permissions to the tables I want to publish) and when
comes the screen to select the DB and the table, there is no table available.

Do I need to give special permissions?

The only difference between him and I is that I have the client installed locally when he uses citrix to connect Nav.

Thanks for your help

kriki

If you have the standard security model in NAV, a user has no access to the SQL server tables directly (if he is not a dbowner or sysadmin [he should NOT be!]).

There are different possibilities you can use:
1) create a new database user with db-datareader database role and use that to connect to the DB through Excel. Easy to maintain, but he has access to all data.
2) create a view to the data he can see and give him access to the view. Takes a little more work, but it is more secure.
3) give him explicit permission to read the table. Easier than 2 but difficult to maintain (it is hidden in all those 1000's of tables).

I think that 2) is the best way. You give only access to the tables+fields he needs to use, but not more and it is even possible to do some totalling in the view or other that the user should do in Excel otherwise.

ben5000

Hi Krikri,

Thanks for your help, I used the second solution you mentioned because the easiest.

But the third one is certainly the best!

Regards,