For some games, it's good. For others, it isn't.
I think that for games where you tell, basically, your own story (Oblivion, Fallout 3, etc.), it's good, because it allows you to create a character and then tell a story based on what YOUR CHARACTER would do. In Fallout 3, I once created a character who was balding (he wore a baseball cap to cover it), had Gordon Freeman-like facial hair and glasses. He was more of a talker than a fighter, and I actually enjoyed playing as him than my previous character, who was the typical badass, minigun-wielding killer. Would The Sims have been as much fun if you could only use a pre-made Sim? I didn't think so.
However, in games that tell the specific story of a specific character (Half-Life 2, Penumbra: Overture, Assassin's Creed, Gears of War, Halo, etc.), it wouldn't make any sense (to me, anyway) to be able to create a character. A specific character is great for a single-player driven, story-focused narrative. Would the Splinter Cell games have been the same if the protagonist was self-created? Or if Half-Life 2 wasn't headed by Gordon Freeman, but instead some green-haired, goofy looking abomination? Or if Halo's protagonist was somebody you created? I didn't think so.
However, I think there's room in both categories for character creation to be either implemented or not added. A forge-your-own-destiny RPG can be great with a specific character. A single-player focused game with a specific, linear story can be great with character customization.
I also think that character customization (from full creation to topic additions) can be implemented in well into multiplayer games. That's why I don't mind hats in TF2. It's a way to make each specific player's class their own, and doesn't destroy balance. (However, unlockable/buyable weapons do kill balance, which is why I'm against them.) I mean, does a guy with a lime green hat really destroy the game THAT much? Players adding their face in Rainbow 6 Vegas didn't do anything to balance, but it did add emotional attachment to the mass of polygons and code that was his/her character.
Well, I guess that's about everything I can say on this subject.