Permission fails for table as view on different db

Kabuto

SQL Server 2000
Navision 4 SP3

I have a table in Navision which is a view in the Navision DB. It calls a table in a different DB. In Query Analyzer I can log on as the user getting an error and it works fine but in Navision i get the "combination user ID and password invalid.." message. If I assign them db_owner it works from Navision.

Any ideas?

kriki

[Topic moved from Navision forum to SQL General forum]

BGI

Is normal behaviour. Is your sql a sql 2005?

when user starts nav client and connects to sql db, following things happens:
1) connect to sql server using sql login
2) connect to db on server using the database login
3) if user is not dbowner, nav client issues a change application role, all other credentials are dropped, and security relies on application role.


So if not dbowner and connecting to other db, sql has no credentials to login on other db.

One solution. In 2e db you want to access, enable guest db login and give this one appropriate rights to tables needed. when no credentials available sql wil try to use guest account.

Kabuto

SQL 2000
Thanks...

That worked.