Many games are great. Depends on the mood I am in to say which I liked the best. THe ones that really captured me are:
Final Fantasy 7
Baldurs Gate (I & II)
Planescape Torment (evem more than Baldurs Gate)
Deus Ex
Independence War
Freespace II
Homeworld and Homeworld: Cataclysm (the first homworld BLEW ME AWAY)
Civilization II
Alpha Centauri (I liked this better then Civ because of the different factions you could be, though basically it was a very similar game)
Masters of Orion (I liked II the best, though I was great as well)
Warcraft (how could we forget this classic?)
C&C: Tiberium Dawn (the first one. Yet another classic. Probably spawned more clones than any other game in history)
Dune (wasn't bad either)
I also remember enjoying the Kings Quest, and Hero's Quest games.
I am sure there are many more that I can't remembe off the top of my head.
Right now I am playing Day of Defeat, a half-life mod. I used to play counter-strike alot, but this mod OWNz CS. At first I didn't like it as much, but it grows on you quick. It is better and it is only the Second beta release. THey still only have half the classes that will be in the full version! I would reccomend it to anyone who like Half-Life/CS.