Login error when trying to run a linked view

frgusto

Hej!

I have a view which is linked object table in Navision.
The view read a table in a different database on the same server. The other database is not a Navision database.
When I try to run the linked object table in Navision i get error:
"Invalid combination of user id and password"

A sysadmin user can run the linked object table in Navision successfully, but all normal users get this error.
In Management studio users can run the view and also the table in the other database, but not in Navision.

Is there any way to get the object linked to a table in another database to work for normal users?

Nav 5.01
SQL-server 2005 sp3

Regards Fredrik

garak

check the permissions for the view(s) on the Non-Navision database for the group public or guest.
if not, set the permissions with grant. Also check, if the users that are connect in the Navision Database (SQL Login or Windows Login), has access to the Non-Navision database.

garak

But this could also be the problem:

https://mbs.microsoft.com/knowledgebase ... -US;971797

frgusto

Thanks for answering Garak.
I have installed the hotfix but to no avail, still the same error.

Did also recheck permissions according to your instructions, but nothing changes.

It's a bit odd the error is a login error, instead of an select not granted error .
Which user is it that may not login...?

Is there anyone who have created a linked table using a view reading a table in another database on the same server?
Are you then able to run the linked table with a user who is not sysadmin?

Regards Fredrik

jannestig

Have you tried just adding an ordinary user to the other database with appropriate permissions ?

kine

The problem could be combination of two things:

1) Permissions for logged user for different database, than NAV, where the user do not have permissions
2) Application roles model - where NAV is using application role to access the objects, and this role has no access to different database.

bbrown

A NAV user access the NAV database via a SQL Application role and therefore does not have access to any resources not granted to the role. In order to access another database (on the same SQL instance) from a linked table, you must take the following steps:

1. Activate the guest account on the other database and grant appropriate permisssions

Use [Other_DB_Name] 
Grant Connect to guest
GO

Grant select, update on dbo.your_table to guest
GO

2. Grant permissions to public on views in NAV

Grant Select, Update on [dbo].[CompanyName$Table For View] to public
GO

frgusto

bBrown, =D>

Thankyou all so very much!