After reading Michaël Samyn's post on achievements I realised that more and more games are implementing this, though not always calling it achievements.
Games like LocoRoco or echochrome do already implement it by not going the "Oh, you died, start all over again" way. They let you keep playing, but giving you stats about how perfect you managed to complete the level.
This takes a lot pressure off the player. He knows he will be able to continue in the story line and won't be stopped short by an insurmountable boss. If I feel challenged, I will be able to improve my stats later on – but it won't limit me from enjoying the story and – most importantly – having fun.
This is probably the second great aspect of achievements: well chosen, they can be quite a source of fun. An excellent example of this is the implementation of Achievements in Geohashing, which range from the obvious (Bicycle Geohash) to the whimsical (Coffin Potato Geohash).
The question is how much the game designer is willing to sacrifice the coherence and immersion of the fictional space in order to provide funny achievements, that may work on the meta level, but not in-game. World of Warcraft travels a fine line there sometimes, by alluding to real-world and therefore off-game culture ("The Old Gnome and the Sea"). It can work since Achievements are kept on the level of the user interface, and not brought into the interaction with the fictional space – at least to my knowledge.
Achievements are fun because they are completely voluntary. As such, they add to the playfulness of a game.