Ownership Chains and Linked Servers

I have 2 servers which are linked. All objects on server1 are owned by sa an
d
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?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?|||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...
>
>|||> 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...[vbcol=seagreen]
> 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:
>