Thanks for your efforts.
My responses inline.
I would just like to clarify a few points:-
1) When you say “delegation settings r done” does that mean you are using the Linked Server security option of “(connections will) Be made using the login’s current security context”?
2) The SP is on the Linked Server, not the Local Server - correct?
No. Sp is on the local server. A Few statements refer to a table in target server. Ex:
Select * from trgserver.trgdbname.dbo.xyz,localdb.dbo.abc where ...
Update trgserver.trgdbname.dbo.xyz set col1 =
3) Are the Owners of the Object (XYZ) and the SP the same?
4) What versions of SQL Server are the Linked and Local Servers?
--SQL 2000 SP2 on both. Local is standard edition. Target is enterprise edition.
Just noticed that the sp is using nolock on local server. Could that be a reason..
Select * from trgserver.trgdbname.dbo.xyz,localdb.dbo.abc (nolock) where ...
Please give some clue..Thanks..