LinkedObject and access rights on SQL Server

Christian_Feitl

Hi all,

I have some troubles with LinkedObject and giving the necessary access rights. Perhaps there is someone that can give me a hint...

I have two databases, one for production and one for a test environment. As there is a table in the prod. env. that changes very often and those changes had to be available just-in-time in the test env. I created a view in the test env. pointing to the apropriate table in the prod. env.

I then set LinkedObject=Yes and LinkedInTransaction=No (following appl.designers guide) and everything works fine in Navision as long as I act as a system administrator on sql server.

As soon as a "normal" user tries to access the table in test env. there appears an error message that the user is not valid in the prod. env.

If I use Query Manager I can access the table even as the normal user.

Hints? Recommendations? Other ideas?

Thanks,
Chris

kine

it is problem of permissions on MS SQL. Workaround> Create the view on the original database and in the test DB create view for this view... than it is working...

DB1: Table
view - select * from Table

DB2: view - select * from DB1.view

Christian_Feitl

First of all thank you for your answer.

There must be something I am doing wrong, because the message persists. The original message is in german so I translate it as good as possible:
"The following SQL Server error happened, while accessing the table "Production Order":
916,"08004",[Microsoft][ODBC SQL Server Driver][SQL Server]The server user 'tatest' is not a valid user in the Prod-database."

I did the following:
1) The user tatest can access both databases with the Navision client
2) The user has all access rights to the original table, the view in the original database and the view in the second database.

Any idea?

Thanks in advance,
Chris

mandyk

try to add the 'tatest' user in production database and don't forget to give this user to have access permission to your tables in production database.

Hope that will solve the issue. [-o<

Christian_Feitl

I checked that again, but my test user has all - in my mind necessary - rights: He has full access to the original table, the view in the prod. db and to the view in the test db.
And I mean full direct access, not only ndo$shadow.
And additionally he is Navision SUPER-user in both dbs.

But I still get the error "user invalid".

Any more ideas? Because I do not have one... :-k

Thanks,
Chris

Christian_Feitl

Microsoft Navision Support could solve the problem:
It is not necessary to define a second view in the original db, but you have to do the following:

1) grant access to user guest to the original db
e.g. do a "exec sp_grantdbaccess guest" in sql query analyzer for the original database.
2) grant access (e.g. SELECT) to the original table to user guest
3) grant access (e.g. SELECT) to group ndo$shadow to the view in the second database.

As far as I understood it is not a problem of Navision client but of the way Navision accesses the database with an application role. They gave me an example to reproduce without Navision.

We decided to use this workaround because it is for testing purposes only but IMHO this is not a reasonable solution for a production environment.

Thanks to all that tried to help!

Chris.