I don't know if this is a true truism, or just an artifact of my experience, though. Certainly plenty of folks have loved roleplaying in settings such as Middle-earth, Star Wars and others, and both TSR and later Wizards of the Coast have popular and profitable novel lines set in their most popular roleplaying settings such as Dragonlance, the Forgotten Realms and Eberron.
I do think that there are some characteristics of a good roleplaying setting and a good fiction setting that set them at odds with each other, though… perhaps making a great setting for one venue poorly suited for the other, at least without a fair bit of work to shoehorn elements in that may not have been there originally.
The most obvious element is that a good roleplaying setting demands a certain level of stasis. It's difficult to find a great roleplaying game in the Star Wars setting in the midst of the Galactic Civil War, which is technically the setting of the original trilogy of movies. Why? Because the best, most crucial, most transformative stuff is being handled by a bunch of NPCs. Luke and Han and Leia and all those guys; they're the ones leading the fight to bring down the Empire and usher in an era of freedom. In the face of that, any game characters played by your group are stuck playing second fiddle.
There are workarounds, but none of them are easy. You can run an alternate history game. You can say that Luke and Han and Leia never happened, or never amounted to much. Leia was never rescued by Luke, because the droids were shot down out of their escape pod after all. Luke went on living a peaceful yet unsatisfying life as a moisture farmer. Han kept smuggling for Jabba until he either sorted out his differences, or bit off more than he could chew and ended up on the butt-end of some bounty-hunter's blaster. Meanwhile, the stolen Death Star schematics somehow came into the hands of the PCs… who now have to be the leaders of the Resistance themselves. Is Star Wars just as compelling a story if it has different characters; different actors?
Or, you could separate them from the movies in time or space. The Knights of the Old Republic roleplaying games for the Xbox and PC did this by going back in time some 3,000 odd years, and essentially rebuilding the Star Wars setting from scratch. This is a satisfying exercise if you enjoy tinkering with settings and putting your own stamp on them (I do) but it's a lot of work, and goes a long way towards defeating the purpose of using an established setting in the first place. To be better suited as a roleplaying setting, the setting needs to be more static; waiting for the PCs to put their stamp on it.Oddly enough, I don't think a lot of people who use static RPG settings really take advantage of letting the PCs put their stamp on stuff. I think a good setting also needs to be vibrant. If there's an implied threat; one nation is wanting to invade another, it's kinda lame that that threat just sits there statically while the PCs are off doing something else entirely. All too often, the PC's actions are nothing more than maintaining the status quo in the face of bad guys who want to change it. I tend to rather like big plots in my games. Invasions, death and succession, game-changing events of various stripes; that's the stuff a lot of folks like in their fantasy novels, why not in their games too? Of course, this means published, static settings are going to be less useful, as you're going along making all kinds of changes to them.
Of course, I almost never use published static settings. For this very reason.
That said, I think there's also an undervaluation of the "small" campaign, both in fiction and in roleplaying games. Why do we have to always "save the world?" Why do we have to do anything big at all? Why is that a feature of the fantasy genre, both in gaming and in fiction?
I honestly am not 100% sure. Another genre I like to read is mainstream thriller stuff. Y'know, Robert Ludlum, John Le Carre, guys like that. Although sometimes they do huge, game-changing type of events, mostly they don't. The success or failure of their protagonists are just one more statistic in the tapestry of the real-world setting. Sometimes, yeah, there's resurgent Nazis, the threat of nuclear destruction, or other things like that, but mostly the changes that happen in this genre are small. That doesn't make the stories they tell any less exciting, dramatic or moving, though.
Anyway, I feel like I'm reaching the stage of my post where I'm starting to ramble, so I'll leave off and leave the question open-ended for the peanut gallery. What makes a setting good for roleplaying? What makes a setting good for fiction? Can the same setting serve both purposes equally well?