Many of the best games ever made started off as mods like CS, TF, Dota, Alien Swarm, DoD, Garry's Mod, Portal, Natural Selection and a lot of the custom game modes people now take for granted like the realistic mode in the MW games started of as mods in the PC version of Cod4. One of the best co-op experiences ever are only available through mods like Synergy and Obsidian Conflict.
I probably would never even been able to play Skyrim if I couldn't change the horrible UI and once you get used to some mods like faster vanilla horses, richer merchants, sounds of Skyrim etc you have a hard time going back. Just relatively small change like adding trees in Whiterun can make quite a lot of difference.
Then of courser there are a lot of mods that aren't especially great or can break or ruin the game but everyone have its own taste. Kinda nice also when you later have finished most of the main quest and gotten tired of the game to then just start playing around with some of the more crazier mods, like playing as a dragon, creating your own apocalypse, transforming people into sweatrolls etc.
As I see it the only people who have any reason to really dislike mods and user created content are the greedy publishers who just wants to sell more of their shitty overpriced dlc and mappacks. However I think if they just pulled their heads out of their asses and look at things more in the long run instead they could see that they have a lot to gain from allowing, encourage and promoting modding and user created content. Just look at how successful Valve have been with titles like TF2, CS and DoD.