Personally I love leveling. For the most part.
I am a huge graphics buff - you can still get me to play a great game with crap-phics if the story or execution is phenomenal, but, you know, I like shiny.... Point being, leveling up is usually associated with a graphic/animation or series of graphics/animations and it always results in a fist pump moment. I feel like, personally, seeing my character briefly illuminated/excited and the knowledge that I have a little more power invests me further into my character and keeps me in the game. Plus, games that make you grind it out a bit, where I feel like I actually did get better and/or deserved to get stronger, really seals the deal for me. In linear, non-open games (Final Fantasy series, Legend of Dragoon (better than FF btw), Fire Emblem, etc), I l-o-v-e working towards levels and attaining new power. It also encourages less skilled players to keep going - didn't find the secret gear? Boss dominating your favorite character? Unable to execute combos? No big, grind out a couple levels and win by stat domination.
However...'for the most part'. Leveling can be a huge detriment to a game. Fallout New Vegas is one of my all-time favorite PC games. I have over 200 hours played, I have seen all of the endings, etc. But I firmly believe the level up system, in tandem with the weapon progression, is broken. The game is crazy fun at the start - you have minimal weapons, you have no stat points, and if you crank the difficulty up you have to kite, execute arms/legs to survive, and think about where you are running unless you want to get blown to pieces repeatedly. Stat progression through leveling keeps you viable, and the new weapons are fun as they appear. However, once you start getting a lot of stats up AND you start finding some of the higher power weapons, the game becomes silly. If you play through the DLC and hit the DLC level cap, the base world monsters can be killed with one stroke from a butter knife. This issue is not excluse to FNV, this is (in my opinion) Bethesda's largest flaw, as it is the same with Skyrim. Master difficulty with fully enchanted weapons and armor is not even remotely challenging.
TL;DR : For leveling to be fun and rewarding in an open world game, the game must progress with you, or at the very least, have a location somewhere that is consistently challenging, no matter how buff your character is.
Completely agree with Tlachtli that some games are awesome because they lack the traditional leveling experience. LoZ is another game that has taught us that minimal gear progression can still yield an excessively entertaining and challenging experience.