Problems with SQL permissions using LinkedObject (views)

Mads

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

DenSter

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.

Mads

Hey DenSter,

I've tried both approaches with the same result

/Mads

DenSter

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.

kine

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... :-(

Mads

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