Problems with SQL permissions using LinkedObject (views)

Mads
Mads Member Posts: 4
edited 2006-02-23 in Navision Attain
Hey,

I've got the following setup:

2 x Attain 3.7B SQL databases (dB1 and dB2) running on the same MS SQL Server (2000 SP4). I use windows login.

The objective is to connect certain tables in dB1 and dB2 through the LinkedObject property on the tables. E.g I create a view in dB2 (v_dB1_Item) that returns all rows from Item table in dB1. This enables me to access all items in dB1 directly from dB2. This approach worked perfectly in my demo setup (where I was set to OWNER of both dB's).

The problem arrises when I implement the functionallity in our live environment, I keep running into insufficient permissions to access the tables in the "other" dB. I've got belong to th SUPER role in both dB's.

What am I doing wrong?

Regards
Mads, Cph

Comments

  • DenSter
    DenSter Member Posts: 8,307
    The user needs to be db_owner in the source database when you do it like that. I could not get it to work any other way. I think someone said you can bypass this:
    - in DB1, create a view into the Item table in DB1
    - in DB2, create a view into the view (not the table) in DB1

    I have not tested this myself though.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • Mads
    Mads Member Posts: 4
    Hey DenSter,

    I've tried both approaches with the same result :(

    /Mads
  • DenSter
    DenSter Member Posts: 8,307
    I didn't think that would work either :-k

    I ended up giving the users that needed write access to the views db_owner, that was the only I cold make it work.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • kine
    kine Member Posts: 12,562
    It is working, you need only more settings... :-) (I wrote about this possibility ;-) See this: http://www.mcse.ms/archive97-2004-9-1036821.html

    there is more info how to enable the chaining...

    ----Edited----
    But it seems, that there is problem with using the application role... I am able to read the data through query analyser, but not from Navision... :-(
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Mads
    Mads Member Posts: 4
    I ended up granting db_ownership to the users in question and subsequentlly restricting theire permissions in Navision.

    I posted the same question to Microsoft and got the following answer :

    ===========================

    RESOLUTION:
    I just tested your scenario successfully, the LinkedObjects functionality worked flawlessly. This is because I was the db_owner at the time of creation. But when I logged in as a Super User, I got a similar error.

    My fruther research showed that in order to use/access this LinkedObject one needs to have a db_owner role at that moment. The functionality is working as designed, however this is not explained in the Application Designer Guide (w1w1adg.pdf). This is an issue so a bug case was opened by the escalation engineer to include this statement in the pdf document. I just got word from the escalation engineer that the document will only be updated in the next major release of Navision, which is Navision 5.0

    STATUS:
    workaround sent

    ACTION PLAN:
    However there is a workaround. You need to create a second role and give these roles the proper rights for using those LinkedObjects, like Select, Delete Inserting and Modifying.

    In my example below with the Guest Role; after granting dbaccess you need to set the rights to the role like mentioned above.

    Use Database
    go
    EXECUTE sp_grantdbaccess guest
    go

Categories