Ding Ding Ding! We have a winner. Despite the existence of a table one could simply join to, this application, in code and at any time during the day, made views out of ALL the possible joins against that security table, using the form "where id in ( 1, 3, 127, 237, 637, ... <snip hundreds of values> 8657, 9872 )." The part I found really creative was that it put the identity value used for each of those joins into the NAME of the corresponding view, then used dynamic SQL to insert the view name into queries at run time. As I write this, there are about 1500 such views.
The hack-around I wrote and posted here runs as a SQL agent job, so, while the basic WTFery is still in play, the agent job silently rewrites any views having the dreaded IN clause with a simple join. It moves the server performance up to "dog slow" from "the phone will not stop ringing because no one can work," which I have to say is a plus. The 5 second delay in my hack is to prevent contention on the view definitions as they get updated.
On the up side, we pointed out to the vendor that this was perhaps ... er ... undesirable, so they jumped right on the solution: keep the basic structure in place, because it's "proven" code that is essential to their whole application, but add some logic that will make the view definition a join in cases where there are less than 8 matches, otherwise it remains an "In" clause. Brillant! Now my job only finds the "smaller" views and fixes them, instead of fixing all of them, and as a bonus we get to keep the overhead of the server and application maintaining them. Yippers.
Good sleuthing, Colin!