I own a 2006 S2000. We got the European market version of the car, and our AP2s still have the F20C, which is limited to ~9300rpm (instead of the F22C you get in the US). Best of both worlds if you ask me
To put it plainly: I love this car.
Mine has 85 000km (52 000 miles) on the clock and has had zero problems. The largest fault to date was the dealer screwing in the Diff Oil filler plug incorrectly that stripped the thread in the aluminium diff enclosure that had to be recoiled.
I am going to go ahead and echo CSG David's statement that the S2000 is a special car. There is something intangible about the pleasure driving this car provides. I've driven cars that are more exciting on paper (read: faster) that just do not convey the same joy, this is a big part of why this particular car is loved by its owners so much. The S2000 is a car you buy because you love driving.
Its not a very practical car (but its more practical than some of its contemporaries like the 350Z, golf clubs or monthly groceries fit in the boot at least...), so I would never recommend it as your only car. If you're especially tall or fat you won't fit inside comfortably or even at all. Many people complain about the ride comfort, but the S2000 is as comfortable as you can expect a sporty roadster to be. By comparison its more comfortable than the Z4 and miles ahead of the SLK. If road noise and engine noise bother you, then you shouldn't be looking at roadsters at all, in my opinion. Another thing people forget is how big of a role tyres play in ride comfort and noise, and the S2000 got a bad rap for the Bridgestones it was released with (that were especially uncomfortable and noisy). If you love engine noise however, there are not a lot of cars on the market (and certainly not for S2000 prices) that sound as good as the S2000 does screaming to 9000rpm...
My S2000 is my daily drive, depending on which office I am going to, I either do 20 mile or 120 mile round trips. And I drive it hard, because that's what it was built for. Its a joy to live with. Few things relax me like driving with the top down in this car after a stressful day. I typically get 450km (just under 300 miles) out of the 50 litre (+- 13 gallon) tank, depends on how much I get stuck in traffic.
The S2000 has proven to be one of the most reliable cars available on the market today, and when it was still in production was topping owner satisfaction surveys. That said, the maintenance is a little more expensive compared to other Hondas, mainly because almost no parts are shared with any other models. I don't know how the maintenance cost will compare to the BRZ, I'd expect it to be higher.
The S2000s are also holding their value really well, and the car is on the road to becoming a real classic. Some have even started appreciating a bit in value, like white with the red leather interior I've noticed going for a premium. If the time comes to sell it you wouldn't lose much. A guy I know is on his third S2000, after selling the previous two after growing tired of it, just to go look for another 6 months later. Someone said earlier they estimate there is about 30k in California alone, but that sounds incorrect since only a 100 000 S2000s were built over its production run worldwide, doubt 30% made its way there, but what do I know. I saw quite a few S2000s when I was in the bay area last year.
A lot of people make a lot of fuss over the twitchy handling. This was mainly an issue with the original AP1 chassis, but it stuck with people and gets blown out of proportion a lot. Take it easy and learn the car before you try and see how fast you can get down a mountain road, just common sense really. It was further blown out of proportion due to how poorly the tyres it was sold with performed in the wet. Hey and guess what, handling at the limit is not relevant to 99% of car owners (even sports cars), because they don't track their cars and are not skilled enough to drive that way anyway. Driving as a skill is not respected enough by the typical male ego, if you ask me, and is not something you will develop doing 80mph down the highway...
If you do decide to go for the S2000 there is a wealth of information available on forums like S2ki to make sure you avoid common problems, both before and after you buy. Do your research and get a car that was properly maintained. Don't buy one if the roof hasn't been taken care of properly, it is quite expensive to replace, and doesn't bode well for how the rest of the car was treated.