Being part of a program that consistently wins championships or at least competes for them is clearly a good thing, especially if you have the talent and drive to be and remain a constant contributor in such a program.
But, of course, not everybody is like that. So a player who might be a role player or bench warmer in a top program might well have a chance to make a consistent contribution in a lesser program.
Of course, every program has its ambitions of somehow becoming a championship program. If it has no such ambition, that's a bad sign. But there will no doubt always be a pecking order -- everywhere except in Lake Wobegon, at least.
Individuals will of course differ. I know they say you should go where you are loved. I'm sure programs love their role players too, though probably a little less. How much love, if any, should you be willing to give up for a chance to be part of championship program, even if you aren't top dog in the pecking order?