mibuso.com
Home› SQL General
Options

Views, different SQL Servers

tro#1tro#1 Member Posts: 122
edited 2007-07-12 in SQL General
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?

Comments

  • Options
    krikikriki Member, Moderator Posts: 9,090
    [Topic moved from Navision forum to SQL General forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • Options
    tro#1tro#1 Member Posts: 122
    We found a solution ourselves. Thanks!
  • Options
    DenSterDenSter Member Posts: 8,304
    Would you mind sharing your solution?
    Daniel Rimmelzwaan
    RIS Plus, LLC
    MVP - Business Apps
  • Options
    tro#1tro#1 Member Posts: 122
    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.
  • Options
    DenSterDenSter Member Posts: 8,304
    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?
    Daniel Rimmelzwaan
    RIS Plus, LLC
    MVP - Business Apps
  • Options
    tro#1tro#1 Member Posts: 122
    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
Sign In or Register to comment.