Views, different SQL Servers

tro#1

We had two databases on one SQL Server:
DB1 and DB2 both on SQL Server A.
Users of DB2 needs to item availability of DB1. We used views from one database to the other one and that worked fine.
In SQL Server Management Studio therefor a user of DB2 needed to have DB-owner rights for DB2. The user has not to be created for DB1 because for DB1 all user had: "Guest account enabled".

Now we had to move DB2 to another Sequal Server B.
We linked SQL Server A to Sequal Server B.
The views work for sysadmins (user ID exists on both SQL Servers) but not for the regular user. So it is still an permission issue.

Have you got any idea which setup would be necessary for the users to work with those views?

kriki

[Topic moved from Navision forum to SQL General forum]

tro#1

We found a solution ourselves. Thanks!

DenSter

Would you mind sharing your solution?

tro#1

Well, I would have told you anyway if I was sure that this is the best solution. So please see it as a workaround how we handle it now. It works but might not be the easiest or best way to solve it.

DB1 on SQL Server A.
DB2 on SQL Server B.

DB2 to has got tables/ views to DB1 because uers of DB2 needs to item availability of DB1.

Navision user setup:
users for DB2 created as usual with permissions within Navision as needed for their tasks (Sales, Finance, etc.)

Sequal Server Management Studio:

SQL Server A:
DB1 needs to have "Guest account enabled".

SQL Server A needs to be a "Linked Server" to SQL Server B.

users for DB2 also needs to be created for SQL Server A (only at the Management Studio not in Navision DB1)
They do not need a "User mapping" but they need to have
Default Database = Master

This is the way it works for our user.

DenSter

That's not a workaround, that's a solution . So then you have linked tables in NAV that link to those views? Or do you have another app that uses those views?

tro#1

DB1 on Sequal Server A
DB2 on Sequal Server B

In DB2 Navision we have got linked tables. On of those tables has got description "ViewItem".

Sequal Server Management Studio for Server B:
We have got a view "ViewItem" (has to be exactly the same name as the describtion of the table in Navision)

The script of this View is:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[ViewItem]
AS
SELECT sq.*
FROM sqnaderau01.[GERMANY].dbo.[Amoena GmbH$Item] as sq
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

sqnaderau01 = Sequal Server A in my description
GERMANY = Database name
Amoena GmbH = Company name