Tali, Thane and Miranda were the ones that stood out to me: all three were a search for family with undertones of betrayal (Jacob's mission is supposedly about the same thing, but it's about as emotionally moving as... well, something really stupid). Shepard gets a chance to be incredibly awesome in some way (defending Tali, stopping the hostage situation, dealing with Miranda's 'old friend'), and all three of your crew members get a chance to show emotions and be incredibly moving: be it Tali talking about her feelings for her father, Thane speaking simply to his son, or Miranda tearing up after talking to her sister. You get a chance to give Tali a hug, but I admit I sort of wanted to give both Thane and Miranda one, too: 'You did your best, and I'm proud of how you handled it.' Then again, I always end up feeling like the crew's mom sometimes considering how much you need to run around and handle everything...
I've yet to play Kasumi, but from what I understand, her mission is pretty up there on the emotionally moving.
I found Mordin and Garrus confronting their past moving as well, if not to the degree of the others. While I don't think Legion's mission itself was overtly emotional, I found the trust he places in you during that mission to be moving: he gives you the choice to reprogram part of his people or destroy them completely and trusts Shepard completely to make the right choice, which is surprisingly... well, wow. A lot of trust in Shep, like I said. Samara and Zaeed's missions were sad in their own ways: dealing with Nef's mother and seeing what his past had turned Zaeed in to stunned me a bit.
Grunt and Jacob... ehhh. Well. Beating up the Thresher Maw was fun?