Even with the exact quest in the game (and it being a faithful representation of a tale in one of the books) the game felt terribly flat. The combat was one dimensional stand and click when the sword glows, learn all the styles. I wanted to love the Witcher so much but the game was plain boring to me.

Resident Evil 1 (psx)
Soul Reaver.
Final Fantasy VIII.

Asides from some of the ones already mentioned like Wild Arms my favorites include:
Xenogears
Ys - The Oath in Felghana
Final Fantasy Tactics
Suikoden III
Lunar: Silver Star Story

NHL 99 :)

Blade Runner

Oddworld: Abe's Oddysee

There are too many.

Age of Empires 2
Empire Earth
Warhammer 40,000: Dawn of War
Chrono Cross
Ys VI: The Ark of Napishtim
Fate/stay night Opening 2

Tower of Time (and maybe Anno 1503)



System Shock's is up there. Unlike many older games, I think this has aged fairly well, apart from the somewhat dated (but quite well-done, for the day) CGI, and the amateurish voice acting for Edward Diego.
Listening to S.H.O.D.A.N.'s voice change to represent its becoming self-aware (and malevolent, and insane) still gives me chills.

Diablo 2
Red Alert 2
F.E.A.R. 2 REBORN DLC
Cybermage: Darklight Awakening (gotta love this comics feeling)
Shadow Warrior 2013
Witcher 2
The Suffering Ties that Bind
Darksiders 1
Vampire the Masquerade (both 1 and 2)

Archimedean Dynasty

And fot the outro:

Warhammer: Dark Omen

Agreed, there are a lot of good ones. MGS and RS were good. I have nostalgia for RS.

Great topic.

Hhmm..

Off the top of my head:

1. C&C Red Alert - No denying this is one of the most epic intros ever.

2. Descent 2

I had an answer but maybe not its probably Homeworld.

For me it's the intro of The Curse of Monkey Island. :)

Thief: The Dark Project

https://www.youtube.com/watch?v=GySn6_aclAM