Mass Effect 1&2 have a lot of emotinal moments - dont need to repeat all of
SpellForce 2 - Shadow Wars
when Shadowsong sings in memory of her dead father and your dead clan after you arrived in the Orc Capital
SpellForce 2 - Dragon Storm
the scene of you dying in the end, your party gathering as you do your final breath and even Yasha showing some affection towards you
StarCraft 2 - Wings Of Libery
the Last Stand of the Protoss against the Hybrids
Warhammer 40k - Dawn Of War
Isador's descent into Chaos and the final stand off with Gabriel in Mission 10 - (and I'm actually a Chaos fan)
Homeworld
Seeing Kharak burning in Mission 3
Legacy Of Kain: Defiance
the outro - Kain feeling "the first bitter taste of that terrible illusion called hope"
FreeSpace 2:
BP: WiH
Ken, Aristeia, One Perfect Moment, Delenda Est, Sunglare
ST:R
Abandon Hope
and now one you might not know
The Shard Of Infinity
As Stars Fall (SoI1-04), War Within A Breath (SoI1-05), The Illusion Of Choice (SoI3-01), Requiem For A Galaxy (SoI3-14)