I usually don't mind some DRM, but as blackened23 said it depends on its intrusiveness (or as I like to say its "aggressiveness"). One type of DRM I don't like is always-on for single-player part(s) of a game specifically. The "best" (or worst) example that comes to mind right away is Diablo III. If there's a server issue, or merely a maintenance you can't play single-player anyway, since you need their server to play, which is of course stupid. I mean it's technically explainable that is, on paper, it makes "sense" (I.E. of course if all the data needed to play is server-side then you're going to need said server to play said game, regardless of you or me liking that fact or not, which is the part that is purely "on paper"). The problem is... it's a stupid concept nonetheless.
I'm trying to picture that in my mind, a meeting of Blizzard employees during the development of D3 at a round table discussing what to do with single-player, and one or more of the guys raise their hand and say "hey, what about single-player requiring our servers too just like multi-player?", they all look at each others in silence, and suddenly breaking the silence one guy hesitantly replies "that's... actually a good idea!". I can't fathom the cognitive "decision-making" process in those guys' mind. But hell... what the heck, it happened anyway. So we the gamers probably don't know what's "good" for us, or rather what's good for them (would it be good for them at all to start with anyhow?). And so we, the gamers, just have the shut the hell up about it, or else. We wanted D3? Well we got it. And it's from our benevolent Blizzard benefactors of all people, we should be happy 'cause they have wings on their back and a halo on their head too.
So yeah I don't like it when always-on DRM spits on single-player, but I actually understand it - and even agree with it - when it comes to multi-player. The same happens with CD-Keys, that's fine by me, for both single-player and multi-player (and I'm not saying that CD-Keys is "enough" or efficient as protection per se, but just saying that I don't mind it at all, regardless of efficiency or lack thereof). Now of course, about Steam, we need it for our Steam games, it's DRM, but again Steam is convenient and almost completely hassle-free. But while I do not have issues with Steam as the platform and application that it is on paper I wouldn't exactly want to rely on too many of such distribution platforms to scatter my digital games library in.
I'd be happy with "just" Steam as my choice, so for example EA making Mass Effect 3 Origin-exclusive pissed me off. It's their right, sure, I'm not debating that. But as a consumer it pissed me off. I still went and bought ME3 anyway and didn't start some sort of petition or never attempted to boycott Origin because of that. I have this tendency of "Utopianizing" (just made this one up, good exercise, try not to twist your tongue on it though) thanks to my "natural naivety", so I like to tell myself something along the lines of "Why is it that EA and Valve just couldn't get along so that ME3 and BF3 gets on Steam just as much as Half-Life, TF2 and Counter-Strike games end up on Origin?". See? Then all you'd need to actually do would simply and merely end up choosing which platform you happen to prefer as a way to store your digital games, period. Simple huh? But, of course, business as usual and "sense" prevents stuff like that from happening. So to help the cause let's add another distribution platform to the market called Origin "because we can" and "because someone else than us is successful at it so let's try to prove that we can do it too, 'cause we're a business too, right?".
But with this said, if something happens to the Steam servers I can still launch it in off-line mode and play the games I own which themselves happen to have single-player modes that don't require an Internet connection at all (and I got plenty of those on Steam). I don't know of many other DRM methods that would allow the "owner" of his/her own games to still be able to play them at all if anything happens to the game pre-launch's identification processes from said DRM. In other words, as I said, Steam is DRM, but if servers at Valve go kaput for 24 hours I can still launch Steam and play many of my games off-line, when in fact just launching Steam itself is a form of identification process since you're log-in into your account, obviously.
Sooo... hmmm, yeah, DRM, what a subject it is.
Oh, I'd like to say this regarding CD Projekt RED. First thing first, I respect those guys very much and wish them all the success they can get (and already got by now, but let's wish them more anyway). Now, the thing is they happen to be successful, but they aren't successful because of their stance on DRM, they are because they made good games (or at worst, good "enough", to be successful "enough"). I firmly believe that The Witcher 2's actual game quality has nothing to do with the fact that there's virtually no DRM in it. Is there DRM for... say... ME3? Yeah (yeah, I.E. "Origin", as much of DRM as Steam itself is), well it sold like hotcakes anyway (or rather I should say like pancakes).
My point is, it's easy for CDPR to have this stance in the industry. You could argue it's not (well I wouldn't 'cause I think it's easy for them), but I'm willing to bet that it was a really huge gamble on their part to have this mentality and apply it for all their games. The pirates won't care if the guys have passion for their work and put their heart, soul and late night sugar-filled coffees at work for their projects, those pirates will still crack, and oh boy was The Witcher 2 pirated. I even believe that removing almost all (if not literally all) DRM from a game simply facilitates piracy. When a game has no DRM and is single-player... you basically ask for it. But despite the risks and lost sales, they still managed, why? Because the game(s) they made are good, that's why. Well, I do put both original Witcher and Witcher 2 in there, some would just point at Witcher 2 as the company's "real first big success", but whatever, ultimately right now CDPR is a big name in the industry which is the point (I.E. point being it's easy for them to adopt this anti-DRM stance).
Now let's say you're an Indie dev and you can't launch your game on Steam for some reason, do you try to put a minimum amount of DRM in your game then? Or do you decide not to, obviously to save money by not doing so at all, and then risking your game getting pirated? Or do you tell yourself that because you're Indie that no one will "bother" pirating your small budget game and that things like that only "happens to others"? I don't think there's any limits in the mind of a pirate who doesn't have enough cash to buy himself a daily meal (but of course has enough to own his actual gaming computer or laptop, obviously). Sure you can be anti-DRM and it's all cool and dandy (don't think this is too much sarcasm though, I actually like their stance and agree with it) but it seems to be meaningful when it comes from a big-enough name in the industry. They probably got enough money now to allow themselves the risks of not having DRM and then coming out and saying "see? it worked, and we're against DRM, this means DRM isn't a good thing and in fact is the worst thing in the gaming industry".
Well, to be completely honest I don't think that even the worst DRM actually is - literally - "the worst thing in the video gaming industry". I think that what's even worse than that in the gaming industry is really bad story-telling (sometimes bad enough as to destroying your perception of a virtual universe after five years of immersion), or cockroach-level of A.I. intelligence (or rather stupidity), or photo-realistic levels of graphics for tech-demo-style games that cry for other memorable (but absent) content, or poorly planned/communicated/organized development and/or too outsourced games in the veins of Colonial Marines, or bad-enough voice acting that gives your shudders in your back (think Diablo III female monk's VA) ... or... do I need to continue?
There's worse, much worse in the gaming industry, within the games themselves, other than DRM per se, in my opinion. So much worse in fact that it arguably contributes to the current state of the industry tenfold above and beyond that of the worst DRM scenario you can think of.