Its like the gun control/gun rights argument. DRM can prevent piracy on paper, but in reality it only serves to annoy and aggravate the law-abiding consumer. Pirates will find ways to get around DRM, just like criminals will get guns regardless of the laws.
That said, I'm all for no-cd patches on PC games, and the like. Being able to alter some part of the software to serve your needs is all good to me because it can help circumvent steps and processes in the software that you personally might not like, for instance mods to skip over intro levels, or allow skipping of cutscenes.
Piracy is downright wrong to me. Some part of the argument i agree with, like how companies do not actually lose out on profit from someone who wasn't going to buy it anyway. But still, the act of stealing something, digital, physical or not, is wrong.
However, I can't stand that when i purchase a game, i have to have an active internet connection to play it, or create an account here or there. These things arent necessary to run the game, games have been without this stuff since they were invented. CD keys? sure fine, but don't go asking me to confirm it every single time.
Even in XBL this is annoying, I don't have a LAN in my house, i have to connect everything physically to the router and only one thing at a time. If I'm downloading something from the internet and want to play a XBLA game, i can't because my xbox can't connect to the server to see if i've actually purchased the games. Even movies and videos you download can't be watched without an active connection.
All in all, I'm for combating piracy, just not in ways that cause inconveniences and aggravation to the actual paying customers.