Re: Ownership Chains and Linked Servers

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 10/28/04 

Date: Thu, 28 Oct 2004 08:47:28 -0500

> Does this mean that the ownership chain will always be broken across
> linked
> servers and so I have to give a user server2 select permisssions so that a
> stored procedure on server1 is able to select from server2?

Your understanding is correct that linked servers effectively break the
ownership chain. You'll need to grant permissions needed on server2 to the
account(s) used for the linked server connection. See sp_addlinkedsrvlogin
in the Books Online for more information. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Stevo" <Stevo@discussions.microsoft.com> wrote in message 
news:AAFC2AE4-08BF-486A-A712-1BE6072B8DB0@microsoft.com...
> Hmm. That's what I feared.
>
> Does this mean that the ownership chain will always be broken across 
> linked
> servers and so I have to give a user server2 select permisssions so that a
> stored procedure on server1 is able to select from server2?
>
> "Dan Guzman" wrote:
>
>> Ownership chaining does not apply to queries across linked servers 
>> because
>> these are essentially different database connections.
>>
>> -- 
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>>
>> "Stevo" <Stevo@discussions.microsoft.com> wrote in message
>> news:02DAAFDA-584E-4148-8B26-A752D6D08A7F@microsoft.com...
>> >I have 2 servers which are linked. All objects on server1 are owned by 
>> >sa
>> >and
>> > all objects on server2 are owned by sa. I have created a mapping 
>> > between
>> > sa
>> > on server1 and sa on server2. However the ownership chain seems to be
>> > broken
>> > when I try and run a stored procedure from server1 which accesses 
>> > objects
>> > on
>> > server2 as a user other than sa.
>> >
>> > Any ideas?
>>
>>
>>