It just worries me that they don't seem to be including a "download" button for more advanced users, it seems like it would be a no-brainer. I'm sure they're familiar enough with the modding process to know it would be useful (almost indispensable for anyone trying to use other sites along with the Workshop.)
This was Steam's idea, guaranteed. They've got to find more ways to box people into their system, dontcha know?
The "download" buttons for more advanced mods are SkyrimNexus, SkyrimForge, TES Alliance and the like. Yes, it would have been nice if Steam Workshop had a facility to re-direct 'advanced mod users' to recommended sites - but that might have looked a little too much like an endorsemant of those sites by a commercial behemoth, trying to squeeze out the 'little guys'. You can bet that there'd be people saying "Oh, those sites paid Bethesda for a mention, guaranteed."
A couple of quotes from Ashley Cheng
More importantly, we want to make it easier for more people to enjoy mods. So we teamed up with Valve and created the Skyrim Workshop. We couldn’t be more pleased with how it’s turning out. Mods are a big part of what makes our games special, so we urge everyone to try it out. We’re going to keep looking for ways to get mods to more people, and hopefully one day to our console audience.
Of course, you’ll still be able to use popular fan-hosted mod sites like Skyrim Nexus to find great mods to play. We did not change any functionality to exclude the way mods worked previously. We even added a few features to help out — for instance, custom INI files can now be packaged into mods so you don’t need to backup your INI files anymore.
Maybe he's a liar - I personally doubt it. I think their intentions are to do a good job for the majority of their customers (most of whom have never used a mod). Of course they can't please everybody and still keep things simple, so if you want complete control of your mods you just don't use Steam Workshop - simple as that.