Really, who needs it? Programmers don’t. To be a good programmer you need to understand how to construct a good algorithm, and some basics of data structures: what they are, and how to use them. You don’t need to be able to implement the ‘classic’ algorithms, as they’re almost always embedded in whatever language you’re using these days. You don’t even often need to know what data structure you’re using, as script languages like perl etc. will handle that for you. Taking a few CS courses can teach you these things, but so can reading a book or even just working on an Open Source project.
Systems administrators certainly don’t need it. Computer operators don’t need it. Even complex fields like security don’t need Computer Science—they need math.
What does a Computer Science degree give you? Primarily you get experience with some language or two that your school has chosen. Often that language is outdated (some schools still teach in Cobol). A good school will most likely train you in Java. Java is good, but what about the new wave of Ruby? How about next week’s language? Really, the targets move so fast, keeping up with them is almost impossible.
So, if anyone has a real use for Computer Science (aside from teaching it), let me know. I’d like to hear of it.

Good question. Personally, I appreciate the foundation of a 4 year degree. It wasn’t about the languages at all for me. Learning the theory of the SDLC and databases was extremely helpful. Basic foundations on data communications, operating systems, and business were also cool. So many companies do things in fubar ways that you might not know better when thrown into Initech.
But, I’ve seen architects, developers, and admins WITHOUT a degree who are the premiere folks who understand this stuff soup to nuts. And, I’ve seen people struggle to “get it” who have a degree from respected Computer Science institutions. In my experience a CSci degree tends to be a decent indicator of ability, but it’s far from the only measure.
I agree with you, the top open source projects seem to run things extremely well, incorporating the best ideas of Computer Science in practical ways.
Also note that most of the people I went to school with graduated as weak programmers unless they had extracurricular projects.