The effect is that a lot of people wasted their time modding their game , and this process is very time consuming(reading each mod instructions carefully, reading the bug reports and posts, executing the steps, starting the game and testing it) and modding is very error prone , some weird bug can trigger late in the game and ruing everything, disabling a mod can cause problems too , finding the cause of a crash is also very time consuming and maybe impossible.
In other games you have the concept of a mod pack (is a bit like a distro) but this is not possible with majority of this Skyrim mods, the Nexus website invented a concept of Collection that is a complex process that might work but has best results if you pay a subscriptions.
Anyway the recent events in open source community made me thingk abut Skyrim community, if Open Source projects with a big downloads or star counter get some money or some badges the developers might fall in the trap of Skyrim moders and wanting to bump their counters will restrict their distribution, I am afraid we will see licenses with restrictions like MIT + GitHub restriction where you can use the code only if you downloaded it from GitHub and from this specific repo(no forks or re hosting allowed). This seems impossible now but as we see in my example with this Skyrim community it will happen slowly.
There are open source and/or non restricted Skryum mod and I think most of this ones are inspired by Free Software philosophy.