- Borderlands --> Borderlands 2. Very funny, the story is good but not amazing. Mostly action-focussed, it's one of those "it's an RPG because it has stats and levels" games.
- System Shock 2 with the Rebirth and STM (Shock Texture Mod) mods. You play a cyborg soldier who wakes up from stasis to find your ship falling apart around you, the crew turned into monsters and something seriously wrong with the computer...
- Deus Ex (the original) with the New Vision and/or HDTP texture mods to make ancient graphics more palatable. Fantastic story and 3 different endings.
- Deux EX: Human Revolution isn't half bad either. Although the story doesn't match up to the original, not many games do.
Though it's actually an MMORPG,
The Secret World is
really worth a look. There's no subscription, you just buy it + the DLC like a single-player game. You don't have to do instanced dungeons or any of the expected MMO stuff, 90% of my time in that game has been spent just playing through the story. The modern-with-magic setting is fascinating, delving into legends, myths, ghost stories, secret societies, conspiracy theories and the like. It doesn't have classes so there's a
lot of customization possible, but what it really nails is atmosphere. In TSW a haunted house
feels like a haunted house. An abandoned soviet bunker feels like just that.
It also has some really difficult and novel puzzles.
Along the same lines (and if you can stomach a star wars game), The Old Republic has 8 classes that each have their own story line. I don't care for the whole jedi/sith theme so I played an Imperial Agent and boy, that storyline is flipping epic. The Republic Trooper's is good, being a war story that starts with you re-building a special forces squad and going after progressively bigger and more important targets and missions. The Smuggler's story line isn't what I'd call epic but it was fun to play, lots of tongue-in-cheek moments.
On the downside SWTOR claims to be free-to-play but is so restrictive that it's better to just subscribe for a month (subscribe then immediately unsubscribe), which should be plenty of time to finish one class story. It also gets a bit repetitive in the later levels, but you'll generally only notice that if you're playing a second character on the same faction.