It is one of those devices that seem almost guaranteed to have trouble with firewalls on both ends. Walter Mossberg agrees.
I'm not sure where I would use one. Though I do spend evenings in hotel rooms without NESN, wishing for a Red Sox fix. I suspect my wife would begin to object when I started changing the channels. To a certain extent, it may be before our time since we view media as 1 or 2 tuners per TV rather than 2 or 3 tuners per house. The next generation of high-end AV amplifiers may revolutionize home wiring as "auxiliary TVs" become slaves to the master stack of tuners (with access to the home video archive via Tivo, access to premium content, etc.). Of course, high-end AV houses haven't realized that Picture in Picture is their problem to solve yet either, and that's been obvious for almost a decade.
The illegal uses are very enticing. Why not put up a bank of them and rent out HBO for $1/hour? Or big Pay per View events. Or automatically rip shows to BitTorrent. But these things didn't need Slingbox to occur.
I came up with a novel business plan a few months ago, where one of the primary sales channels would be repurposing for illegal use. The only question is whether it is intentionally hard or intentionally easy to hack to Slingbox.
Edit: Thanks for the comment Charlie. It's true that Slingbox has its own tuner (thus you can change channels). Unfortunately, most of what I would want to see is on digital cable and requires a cable box to decrypt.