server, join, same query, different plans for different use

We recently moved a database from one SQL server to another and replicated
logins, users, permissions, etc. The problem occurs when joins are run from
the original server, referencing tables on the new server, using the
four-part name. Using the same query, some users time out with an execution
plan that looks like it ignores the indexes on the remote server. Other's
execute in seconds.
I've been over permissions with a fine-tooth comb. The users who succeed are
domain or SQL admins. Those who fail are members of a domain group, specified
as a login and database user with select priviledges on all tables and views.
Ownership is sa, just as it is for databases on the original server. Can
anyone suggest what else I might look for? Thanks!
--
Thanks,
CGWThe permissions across the link are based on the logins associated with the
link. You may have set them up with user accounts on the remote server, but
they credentials passed to that remote server are those configured for the
link. My guess is that you need to set up for users to make connections
"using the login's current security context". In EM you can check under the
Security tab on your linked server properties or run sp_helplinkedsrvlogin in
QA to see what you get.
HTH,
John Scragg
"CGW" wrote:
> We recently moved a database from one SQL server to another and replicated
> logins, users, permissions, etc. The problem occurs when joins are run from
> the original server, referencing tables on the new server, using the
> four-part name. Using the same query, some users time out with an execution
> plan that looks like it ignores the indexes on the remote server. Other's
> execute in seconds.
> I've been over permissions with a fine-tooth comb. The users who succeed are
> domain or SQL admins. Those who fail are members of a domain group, specified
> as a login and database user with select priviledges on all tables and views.
> Ownership is sa, just as it is for databases on the original server. Can
> anyone suggest what else I might look for? Thanks!
> --
> Thanks,
> CGW|||Good guess, but actually, that is how they are set up. What else do you think
might be the problem?
--
Thanks,
CGW
"John Scragg" wrote:
> The permissions across the link are based on the logins associated with the
> link. You may have set them up with user accounts on the remote server, but
> they credentials passed to that remote server are those configured for the
> link. My guess is that you need to set up for users to make connections
> "using the login's current security context". In EM you can check under the
> Security tab on your linked server properties or run sp_helplinkedsrvlogin in
> QA to see what you get.
> HTH,
> John Scragg
> "CGW" wrote:
> > We recently moved a database from one SQL server to another and replicated
> > logins, users, permissions, etc. The problem occurs when joins are run from
> > the original server, referencing tables on the new server, using the
> > four-part name. Using the same query, some users time out with an execution
> > plan that looks like it ignores the indexes on the remote server. Other's
> > execute in seconds.
> >
> > I've been over permissions with a fine-tooth comb. The users who succeed are
> > domain or SQL admins. Those who fail are members of a domain group, specified
> > as a login and database user with select priviledges on all tables and views.
> >
> > Ownership is sa, just as it is for databases on the original server. Can
> > anyone suggest what else I might look for? Thanks!
> > --
> > Thanks,
> >
> > CGW|||Dunno. Maybe the ownership chain is broken. If the admin roles work, it may
be the case that the chain is broken for regular users.
Using Ownership Chaining (http://tinyurl.com/8q5dq)
Cross DB Ownership Chaining (http://tinyurl.com/7n9jf)
Best of luck,
John
"CGW" wrote:
> Good guess, but actually, that is how they are set up. What else do you think
> might be the problem?
> --
> Thanks,
> CGW
>
> "John Scragg" wrote:
> > The permissions across the link are based on the logins associated with the
> > link. You may have set them up with user accounts on the remote server, but
> > they credentials passed to that remote server are those configured for the
> > link. My guess is that you need to set up for users to make connections
> > "using the login's current security context". In EM you can check under the
> > Security tab on your linked server properties or run sp_helplinkedsrvlogin in
> > QA to see what you get.
> >
> > HTH,
> >
> > John Scragg
> >
> > "CGW" wrote:
> >
> > > We recently moved a database from one SQL server to another and replicated
> > > logins, users, permissions, etc. The problem occurs when joins are run from
> > > the original server, referencing tables on the new server, using the
> > > four-part name. Using the same query, some users time out with an execution
> > > plan that looks like it ignores the indexes on the remote server. Other's
> > > execute in seconds.
> > >
> > > I've been over permissions with a fine-tooth comb. The users who succeed are
> > > domain or SQL admins. Those who fail are members of a domain group, specified
> > > as a login and database user with select priviledges on all tables and views.
> > >
> > > Ownership is sa, just as it is for databases on the original server. Can
> > > anyone suggest what else I might look for? Thanks!
> > > --
> > > Thanks,
> > >
> > > CGW|||Thanks, we're still researching. When we link with a specific security
context, we can make the problem go away, which gave us some clues.
--
Thanks,
CGW
"John Scragg" wrote:
> Dunno. Maybe the ownership chain is broken. If the admin roles work, it may
> be the case that the chain is broken for regular users.
> Using Ownership Chaining (http://tinyurl.com/8q5dq)
> Cross DB Ownership Chaining (http://tinyurl.com/7n9jf)
> Best of luck,
> John
> "CGW" wrote:
> > Good guess, but actually, that is how they are set up. What else do you think
> > might be the problem?
> > --
> > Thanks,
> >
> > CGW
> >
> >
> > "John Scragg" wrote:
> >
> > > The permissions across the link are based on the logins associated with the
> > > link. You may have set them up with user accounts on the remote server, but
> > > they credentials passed to that remote server are those configured for the
> > > link. My guess is that you need to set up for users to make connections
> > > "using the login's current security context". In EM you can check under the
> > > Security tab on your linked server properties or run sp_helplinkedsrvlogin in
> > > QA to see what you get.
> > >
> > > HTH,
> > >
> > > John Scragg
> > >
> > > "CGW" wrote:
> > >
> > > > We recently moved a database from one SQL server to another and replicated
> > > > logins, users, permissions, etc. The problem occurs when joins are run from
> > > > the original server, referencing tables on the new server, using the
> > > > four-part name. Using the same query, some users time out with an execution
> > > > plan that looks like it ignores the indexes on the remote server. Other's
> > > > execute in seconds.
> > > >
> > > > I've been over permissions with a fine-tooth comb. The users who succeed are
> > > > domain or SQL admins. Those who fail are members of a domain group, specified
> > > > as a login and database user with select priviledges on all tables and views.
> > > >
> > > > Ownership is sa, just as it is for databases on the original server. Can
> > > > anyone suggest what else I might look for? Thanks!
> > > > --
> > > > Thanks,
> > > >
> > > > CGW