Well the major complaints about it is that you need to use it to play the newer games, and sometimes it can be a bit quirky (like it has the ability to play games while not connected to the internet, but sometimes it won't let you). Also by default it updates games automatically, meaning if you wanna play a game you'll have to wait for the updates to finish if it started downloading 'em. (Of course, if it's an MP game you'd have to patch anyways.)
Also, the anti-cheat system rules with an iron fist. If someone is caught cheating they lose the ability to play in anti-cheat enabled games (VAC servers) FOREVER. Even if it was your little brother or your best friend, tough luck. You either live with it or create a new steam account and buy the game you were banned from again (though Valve frowns on this).
The simple solution to this possibility is to keep your account to yourself, which means no sharing games. I don't have a problem with this, since I wouldn't share my debit card and pin number with anyone.
But I find it quite nice. It keeps my games up to date automatically, as I said, so I don't have to go patch hunting when I install a game and I'm guaranteed not to miss a patch and then get stuck wondering why multiplayer isn't working.
Also, if I purchase a game from the Steam Store, I don't have to even wait for my credit card to clear. The game is available for download immediately (assuming it's released of course). Beats having to drive to the store for a game.
Also the integration of third party mods is ok. Although I'm wishing it was a bit more (a larger DB of third-party mods listed in the store would be welcome), having all the games in one spot is pretty slick.
It's also got a built-in backup tool that'll split the files into the correct sizes for burning to CD/DVD, and generates a small EXE file you can run to restore your backup for you. Of course, backing up is only needed if you don't have a fast internet connection, because you can always redownload the game files from Steam at any time.
You can also run Steam on as many computers as you want at once with your account and download and run the games on as many computers as you want. However, of course, an account can only be in ONE multiplayer game at a time (LAN games don't count so you can still throw a lan party of any size with only one account), which is a pretty light restriction all things considered.