At the end of Metal Gear Solid 3, you have to kill one of the main characters (trying to keep this spoiler-free), and, instead of a cut scene, the game forces the player (i.e. you) to pull the trigger manually. Likewise, at the end of Shadow of the Colossus, you have control but you cannot prevent the inevitable from happening. The inevitability and lack of control is what makes it tragic (see every tragedy written for the last 3000 years). Games provide an interesting medium for this, since the gamer has a great deal of control throughout the game. When real control is taken away and the gamer is forced to do something they don't want to do (and they can't go back), it enhances the emotional response. Just look at how many rumors and hacks sprung up to resurrect Aeris in FFVII.
Metal Gear Solid 3 is a great example of this, since you can play the entire game without killing anyone, and it still forces you to carry out the execution at the end. Compare to a similar scene in Metal Gear Solid 1 when Snake battles Sniper Wolf and he does a similar execution in a cut scene. The scene in MGS3 is much more poignant because the player is forced to be a part of the action.
To sum up, yeah, games (good ones) can make you cry.