linked server query exec plan problems | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

linked server query exec plan problems

With SQL 2000 EE, we run a query that joins 3 tables from the local server with one table from a linked server.
When we run this query as ‘sa#%92, we get a decent execution plan and response time, 400ms.
When we run it as another user, ‘express#%92, which is not a System Admin, we get a bad plan that takes much longer, 7 seconds. In EM, we have one Local Login configured under the Properties/Security tab for the Linked Server. That login is ‘sa#%92 and uses a remote login on the linked server which is a System Admin. All other users are supposed to use their current security context to login to the linked server. If I change this, and add the ‘express#%92 login to the list of Local Logins for the linked server, and tell it to log in on the linked server as a System Admin user,
then the query gets the same execution plan as it does when running it as ‘sa#%92. Basically the role of the user login on the linked server is affecting the execution plan.
We cannot leave it so that the ‘express’ user logs in as System Admin for security reasons.
Any ideas for a work around?
Thanks!
tunia
Are you using fully qualified names fro the table as Server.DBName.OwnerName.ObjName? Gaurav
Yes, we do qualify the entire name such as Server.DBName.OwnerName.ObjName for the linked server table, and for the local server tables we use DBName..TableName. tunia
What are the privileges for those 3 tables and one table on linked server for this user account?
Try to capture events using PROFILER during this activity. _________
Satya SKJ
The ‘express’ login has the same database roles on the local and linked server for all the databases being accessed – public and db_datareader. No server role at all- neither local nor linked server. The ‘sa’ login has database role public and db_owner, as well as sysadmin server role.
tunia
The ‘express’ login has the same database roles on the local and linked server for all the databases being accessed – public and db_datareader. No server role at all- neither local nor linked server. The ‘sa’ login has database role public and db_owner, as well as sysadmin server role.
tunia
]]>