There are at least two issues that make mandatory Steamworks a Bad Thing^TM.
1) A lot of people have slow / unreliable Internet connection, and have trouble with games that need activation.
"In this day and age, everybody has fast internet."
Everybody you know, perhaps. In many countries the internet is 5-10 years behind. In western Europe we are very lucky. I guess the same goes for USA. Africa, on the other hand...
http://www.kwintessential.co.uk/map/internet-penetration.html
Another thing is the payment model for internet. It's quite rare here in Denmark to pay by data volume. In some countries that's the way they do it. Having a mandatory gigabyte-sized update on release day is essentially an added cost that in some countries could be comparable to the price of the game. The game won't run at all without these 'day one' updates. Granted, it's not Steamworks' fault that developers choose to do this, but apparently the temptation to do so is irresistable for a lot of developers - I guess it means they can send the boxes to the shops without actually finishing the game. Without Steamworks the temptation is not there.
"I have fast internet, so that's not a problem for me."
Good for you. I also have fast internet. Even though I chose the slowest internet connection my provider offers (30mbit up/down, unlimited data).
The point being, access to fast broadband is very dependant on the country you live in, and whether you live in the city or in the middle of nowhere. It's not a problem for me or you, but it's a problem for a huge number of people around the world, some of which really want to play the game.
2) Control (and possibility of activation in 15 years' time)
I don't really agree with the people saying that PC gaming is dying. A lot of the more mainstream titles are selling better on consoles, perhaps, but the creativity on display in smaller PC titles right now is astounding. It's a cyclical thing, and I remember the same discussion in the days of the Playstation 1 and Sega Saturn.
But IF you believe that PC gaming is in trouble, why would you want to give the keys to your newly-purchased game to a company that makes its money from PC gaming? There is no guarantee that they won't close down at some point.
In a bankruptcy situation, the original employees most likely do NOT have the power to spend time / wages on stuff that does not benefit the shareholders - who want to rescue as much of their money as possible. Stuff like going through their back catalogues and unlocking all the games so that they no longer require activation.
"But the reason PC gaming is dying is because of piracy. Steam prevents piracy."
No it doesn't. Steam games get cracked about as quickly as non-Steam games. I have no idea if it's more difficult for the crackers, but it doesn't seem to slow them down very much (and the challenge is surely what makes it fun for these people?). Stardock used to release games with a minimum of DRM. They got pirated, like anything else, but apparently they also sold well (at least according to the hype).
There is also the wider issue of control. You buy the game, it would be nice to have complete control over it. Install it on a computer that is not connected to the internet, install it in whatever folder you want, etc.
I actually think Steam is a really handy tool for some games, especially cheap action games that I just want to play through a couple of times. But Skyrim is a game that a) is completely single-player and thus has little advantage of Steamworks features (online saves are cool, but a lot of us presumably only have access to one computer powerful enough to play Skyrim anyway, and as soon as mods come into the equation it becomes very difficult to play a save on other machines), and B) is hopefully the sort of game we will want to replay 20 years from now, leading to concern about the activation process etc. Therefore, MANDATORY Steam is IMHO a bad idea.
I agree with the general points you made, but I'd like to correct you on some details.
The purpose of DRM is not to prevent all piracy forever. The purpose of DRM is to prevent two specific kinds of piracy: casual piracy and day zero piracy. Casual piracy means being able to simply share the game with your friends instead of everybody having to buy their own copy. And day zero piracy is when the pirated version of the game is available for download on the same day when the game is released. Nowadays major titles make the most sales in the first week after release so preventing piracy in that first week is by far the most crucial. Think about it, they announced Skyrim in December and the marketing machine will be hyping it more and more up to release. A lot of people can already hardly wait to play the game and when the game is fianlly released many potential pirates will rather buy the game immediately instead of having to wait another week before they could get their hands on a pirated copy.
So I can't really begrudge any company for using DRM. However, once whatever copy protection the game is using is broken all further copies of the game should in my opinion be sold without any copy protection harsher than a disc check (to prevent casual piracy).
For more information on DRM and piracy, here is a link to an excellent article on the topic: http://www.tweakguides.com/Piracy_1.html
Anyway, my stance is that optional STEAM for people who want to use it = great, but mandatory STEAM for everyone = bad. And I really hate the idea of online activation for singleplayer games. It's my firm belief that if I buy a retail copy of a game I shouldn't need a bloody internet connection to install and play it.