Write to view

toor

Hi folks,

i've a nav 4.0 db on sql server 2005 with standard security model.

I've on the same server a second database. In the Nav db i've created a view that selects data from one table in the second db.

I want to write to that view from Nav. I'm only able to do this by granting the user sysadmin rights. No other rights seems to work. I think because of the application role that navision uses.

I can write to the view with a non sysadmin user from Management Studio.

Do you have any ideas how to write to the view from nav with a non sysadmin user?

thanks & regards
tobias

kine

Check the property LinkedInTransaction of the table in NAV, may be it will helps you, but it is just a tip. Another way can be to assign the permissions manually on the view.

bbrown

With application roles access to objects not defined in the application role is granted via the guest account.

Lars_Westman

Just remember to be careful if the second database is in a linked server.

SQL can't maintain transactional integrity between linked servers and if You get an error in Your code the changes won't be rolled back in the linked server.

bbrown

The LinkedInTransaction property must be NO if the view references objects outside the current database.

I assume your reference to transactional integrity is in the context of Navision linked tables. SQL itself has no issues supporting transactions distributed across multiple databases and servers. There are of course restrictions depending on the data providers

Lars_Westman

bbrown wrote:

The LinkedInTransaction property must be NO if the view references objects outside the current database.

I assume your reference to transactional integrity is in the context of Navision linked tables. SQL itself has no issues supporting transactions distributed across multiple databases and servers. There are of course restrictions depending on the data providers

Yes I mean linked tables in Navision that references data on a linked server. Then you must watch out when updating data in the linked server when you e.g. is doing updates when posting.

BGI

toor wrote:

Hi folks,
.... I'm only able to do this by granting the user sysadmin rights. No other rights seems to work. I think because of the application role that navision uses.

I think you are correct in the statement that it is the application role that is the problem.
Once you change to an application role, the login security context is lost, so sql has no way of validating you on the second database.

Raised this question with MS, but they told us the only solution is dbowner (or sysadmin) option...

But it don't think this is correct: we resolve this problem by giving the user "guest" select and connect rights on the second database. In our case we only need to select data via a view in a second database. If you want to write data into the other database using a view i suppose there are ohter rights you need to grant to the "guest" user...

Rgds
Benny