Steam is DRM done right. Lets face it DRM is a part of gaming that is not going away, so we might as well support the DRM that actually adds some value to a product, and Steam is the best so far. The ability to build a library of games that I can install and re-install games when ever I want is valuable to me, and add to that the cloud storage of saves, and lets not forget the crazy sale prices on games, and we end up with a DRM that not only I can live with, but can be happy about.
I think part of the key to Steam being successful is that they have built a reputation of being trustworthy. I'm not afraid that if I buy a game that uses Steamworks that it will stop working in a month or two when they decide that particular game is not profitable enough to continue hosting. Even games that no longer are sold on Steam still work after they take them down.
No, Steam is an example of DRM that blows. It does nothing for me, and I would call it wholly unsuccessful. After the 3rd hour spent trying to troubleshoot the Steam client during a Bioshock or Portal install awhile back (I can't even remember which it was now... might've suffered through both), I came up with an algorithm that is a far better use of my time: I wait until No Steam cracks are developed for whatever game I'm interested in and some critical mass of posts appear from people that convince me that it is working well, then I purchase the game in a store on DVD as god intended and as I would have earlier had it been available without the Steam requirement, with cash, telling the obnoxious Target clerk that tries to scan my driver's license for marketing purposes to get bent in the nicest possible way, and then I enjoy the game like I should've been able to in the first place.
For anyone that's thinking of saying "Oh, you just want to pirate the game", get bent in the nicest possible way. I'm a nerd with a grey beard and a room full of awesome games in their retail boxes that I've bought over several decades. I'm a programmer, and I enjoy rewarding other programmers for their efforts, as I have been rewarded, especially if I'm enjoying those efforts. I've got a CP/M machine with the original Crowther Adventure on it (okay, I admit that I downloaded that one from a BBS in the 80s, but there wasn't anywhere to buy it). I've got a machine with MS-DOS 5.0 on it when I want to put a smile on someone's face by showing them the original Doom or Descent. 99% of the games I own came with no DRM at all. I even bought a couple copies of the original Quake just to have them in the original shrinkwrap. What I don't do is reward DRM because it punishes me, the customer. I logged hundreds of hours in Oblivion, and it's one of my favorite all-time games. I bought it three times, once for me years ago, once for my wife, and once when I was on a trip and wanted to install it on a friend's machine. I might buy Skyrim six months from now after there's a No Steam patch for it or I might buy it a year from now or I might buy it never. What I will not do, however, is let it or any other game I buy make an outbound connection from whatever machine I install it on for the sole purpose of negotiating a DRM checkpoint. If I don't have a reasonable certainty that I can play it without it doing that, then I don't buy it. I don't let TSA agents grope my genitals either.
Steam trustworthy? The good people at Valve have good intentions, and I'm sure that they're trustworthy, but there is much beyond their control like their client not sucking on certain machine configurations or a bunch of Steam accounts getting hacked a few days before the Skyrim release. For people like me who aren't likely to ever create a Steam account with a throwaway email address or any other, it's just another moving part to break. For a single-player game like Skyrim, all it is is DRM. Nothing else. And DRM is a big minus to someone who wants to buy the game and play it.