I have a love/hate relationship with achievements. At one point when I had a gameznflix membership I would rent games I didn't particularly care for just so I could get points, which I soon found was pretty useless. When I was doing that though I was trying to compete with Dobio and Matt, both of them have a high gamerscore and mine was close at the time, and it was fun trying to play catch up, but after a while it bored me and I canceled my account and stopped caring as much and now I'm pretty far behind them. With that said...
I like them because sometimes they will get me to replay a game I normally wouldn't to try and get a more difficult achievement. I usually won't go after one if I don't enjoy doing it though. Take GH III for instance. I beat the game on expert and got that achievement, but it doesn't give you the achievements for beating it on easy, medium and hard, which is ridiculous. I hate games that do that. If you beat something on a higher difficulty, you should get all difficulty achievements below that. Some games do, some don't, and GH III doesn't. I don't want to waste time playing GH III on easy when it's not going to be any fun, just for an achievement.
However, I think there is a negative effect of gamerscore and achievements. They are completely overblown, but it's that collection/RPG element of progression that sucks people in. CoD4 does it with prestige mode, and it works. You give somebody a long term goal in a video game and there will be people who go for it, and achievements are as long as it gets. But, games can suffer because of it. Sometimes games incorporate achievements into multiplayer, and they aren't just simple things like "kill a total of 500 enemies online." They are things like "Run over 4 peoples in less than 10 seconds." or "Kill 5 enemies without dying using only a wrench." Those are just fake examples I came up with, but there are ones similar to that. It makes online suck because of the people out there who will just farm for these achievements instead of actually playing the game. You continually run into games where players are just trying to earn points, and it makes your team or the opposing team suffer because of it. If they took out silly online achievements like that, and made them all more basic like "Kill X amount of players in deathmatch" it wouldn't be as bad.
When Microsoft employees first presented the idea of achievements and gamerscore, I somehow doubt the conversation went, "I have this fun idea that I think gamers will really enjoy. All games you play will allow you to earn global points that all of your friends can see, and medals that you can show off, for achieving certain goals in the games created by the developers! This will make gaming more competitive and give users a chance to feel like they've accomplished something." It probably went more like this, "I have this good idea, I call them achievements. It will be seriously addicting and allow us to get more money from customers."