Is it still that case that any join (heterogeneous to another SQL*Server or to some other DB, say Oracle) via a linked server db table and a local db table will basically ignore any relevant remote table where clause constraints and bring the whole remote table locally before then executing the constraints?

This is not obvious to track or assess, but temp space starts to fill up and hasn't always been released readily once the query finished.

The author's advice to create a local #temp table with the remote data essentially does this latter remote copy explicitly and then does the join locally.

I have avoided heterogeneous joins between linked servers and find SSIS is vastly faster to accomplish such tasks.

It might be worth having the links though for development purposes to hit up against remote tables for the sake of schema and data assessment.

The descriptions people give below of the security issues make it seem someone tedious to manage robustly with a fine granularity of privilege control.

It was a good article in terms of explaining the ways you can set up linked servers though and I want to thank the author for his contribution.