As almost everyone says, it depends on the game.
In Half-Life 2 and episodes, for example, I always play on hard, on the lower difficulties the enemies are too weak and ammunition is far too plentiful (I don't exactly hoard ammunition, I go through plenty of it, it's just that most of the ammunition I expend is right on the mark so I don't waste it)
In Call of Duty 2, I'm constantly torn between Hardened and Veteran. Veteran is more atmospheric in half of the missions in the game (the easier ones), because you just have to make better use of cover and be more deliberate in your movements to avoid being killed. The rest of the missions, however, allow you almost no tactical flexibility when confronting you with endlessly spawning enemies at every goddamn objective and forcing you to carve right through entire squads to get the job done.
I like a genuine challenge. If there's one thing I hold a passionate hatred for, it's games that cheat to make it harder for you. For example, racing games with a "catch up" feature that, in the name of making it more challenging for you if the game thinks you're "too far ahead", 'fast-forward' your AI opponents who are lagging behind right up to your tail. It sounds like a nice balancing tool in theory, but in practice, it means that you can do one of your best runs, drive smoothly for the entire race and take every corner perfectly, and still lose to an AI opponent who crashes at every turn because their augmented, rubber-band handling slingshots them around the final corner, right past you and over the finish line, all in the interest of 'balancing'. It basically makes any attempt at putting distance between yourself and the opponents behind you futile. It's actually safer to leave them right behind you, snapping at your heels so the catch-up won't kick in.
That's what I enjoy about a lot of tactical shooters, they usually don't give you OR your opponents any special breaks. Of course there are often logistical differences, one side might have more support assets such as air support, or better equipment... but in a one-on-one fight with an enemy soldier, it's a fair fight, not a bullet-soaking hero versus one of many cannon fodder. If I take on an enemy in a fair fight and lose due to a tactical blunder or even just a bit of bad luck, I don't mind, but when a game uses all sorts of contrived, obvious methods to make it 'more challenging', I get angry.