Something like Neverwinter Nights that allows for some kind of campaign creation and sharing (or which has a decent pool of different campaigns to choose from). Preferably modern-ish.

If you just want the campaign creation and sharing, there's Unlimited Adventures (Forgotton Realms Collection 2) and Blades of Avernum (I believe in Avernum 1-6). Neither game is multi-player or modern, however.

Vampire the Masquerade Redemption

Divinity OS is built for coop play.

Can't believe I forgot about Arcanum.

Divinity Original Sin.

Get the enhanced edition, coming later next month.

Does that have a lot of mods ?

Divinity Original Sin has a good original co-op campaign. It has mod tools, but I've heard they are difficult to use. The sequel is not yet out but is supposed to have a team dedicated to making mod and GM tools.

This probably isn't exactly what you're looking for, but I've had a lot of co-op fun with Torchlight 2.

If you don't mind it being cyberpunk instead of classic fantasy, Shadowrun is a pretty story and it has tools that allow for custom campaings. I haven't tested these yet though.

Neverwinter Nights 1 and 2
Baldur's Gate 1 and 2
Icewind Dale 1 and 2
Divinity Original Sin
Arcanum
Lionheart - Legacy of the Crusader (Strangely listed as Singleplayer only on the store page)
Sacred 2 (it's more of an action RPG)

Well Divinity Original Sin would probably have you covered.

I'll also second Shadowrun. It already has creation tools and I believe the newest edition they're working on right now will have multiplayer.

As more of an outlier coop suggestion I would also recommend Borderlands 2 if you are ok with a fps/rpg. The initial campaign is a bit underwhelming in terms of story but makes up for it with it's sense of style and humor. The DLCs are where the game really shines with some truly absurd humor and new over the top characters. And if you're yearning for D&D Tiny Tina's DLC gives you the swords and sorcery aesthetic.

It's only on steam but Castle Crashers is quite fun. Plays exactly the same multi-player as single-player. It might be lighter than what you're looking for though.
Edit: doesn't have any campaign creation or more than one campaign, but it's still a fun multi-player game, and you could play it through more than once with different characters.

Some good recommendations here. I'll throw this upcoming title to you, it's called Sword Coast Legends. Check out Champions or Dark Alliance games also (action RPGs on PS2, though).

I haven't played it in a while - but it had a fair amount when I stopped playing.