The answer is a firm... DEPENDS.
Not to be evasive, but the entire way a game works is fundamentally influenced by whether or not a game is played online. If a game is "stand alone" and works without a net connection, then you can simply do things that would not be possible in an online environment. Take Morrowind, for example- you could mod the hell out of it, make yourself all-powerful, add weapons, graphics, sounds, dungeons, etc. etc. etc. etc. because it was ONLY YOU playing. if they ever tried putting out a "Morrowind Online", then everythig would have to be "balanced" so that the game is "fair" for everyone.
MMOs, as good as they are, suffer from a HUGE lack of expandable content- unless the developer makes it (and carefully, one might say even almost OBSESSIVELY "balances" everything) then it can't exist in-game. MMOs also get changed all the time, "nerfing" some character classes that are found to be unfairly powerful or that have an exploitabe feature. And let's not forget that the moment a Publisher stops paying their server bills, then you can no longer play, no matter how big a fan you are, so you had better hope that the game's popular.
I dream of the day that someone will publish an MMO with expandable, user-written content. I can imagine walking into a cave, and having a warning flash: "You are leaving the world you know for a user-made instance- please be aware that the rules as you know them as well as the ESRB rating of the content might go by the wayside. Do you want to proceed (Y/N)?". God knows *I'd* play it, seeing as how most of the best content I've ever experienced in a game like Morrowind or Neverwinter Nights was written BY fans FOR fans. I won't hold my breath, however...
A game that *I* thought worked equally well in Single-Player and Multiplayer was the Unreal Tournament series... Botmatch on a stand-alone PC was very challenging and multiplayer was equally fun, but you really never HAD TO do one or the other- it was totally up to you. User-made maps, total conversions and everything else was possible for both audiences, since the game used a client/server model that relied on the users' PCs to run game instances.