In No Particular Order:
Fallout 3 - the way this game starts is amazing especially when u first see the post apocalyptic world.. very cinematic, the music from the 40's brings u into this empty world.. it kinda gives me that feeling u get in an old folks home like u know somebody just passed or will be passing real soon.. a depressing look at a "what if" of our world..
Lost Odyssey - you start off in a war of thousands, you are an immortal warrior, and nothing is more bad ass than being the lone survivor of the war.. your cutting through groups of soldiers, attacking weak points on a tanklike boss type.. then a meteor falls, actually i dont want to spoil it but its a great beginning for an even greater game
Grand Theft Auto San Andreas - your mother just passed, u come home for the funeral.. the local police pick u up and ur already in a load of shit.. not only that but they drop u off in enemy territory and the only way home is on a bmx.. from there its like a john singleton or hughes brothers film..
Fable2 - this game starts on dark starry night on the poor side of town.. a peter jackson-esque orchestra plays in the background and gives the game an instant mystical feel.. u see a crowd of people scuffling through the city, and then a bird flies by and then the bird drops crap right onto your character's face.. u start off somewhat homeless with no one but u and ur sister.. from there the fable begins..
Bioshock - your on a plane, it crashes right into the ocean.. u survive the crash and gain control as soon as u get your first gasp of air.. u see nothing but water, as u look around u notice this tower like building in the middle of the ocean.. u enter the structure and take a ride in an elevator type machine.. as soon as u reach the bottom floor, someone gets murdered before your eyes, and the monster tries to break into ur elevator.. the doors open and u now begin this journey..
im such a video game nerd that i could go on for pages about game intros that i loved.. so i'll leave it at these because they were first in mind..