I use them all the time.
I hate having to insert the goddamn CD/DVD in everytime I wanna play a game, especially after a 14 hour day at work and I wanna kill people in UT2K4, I dont have the patience to dig out the DVD for it.
But here's a word of advice, always backup the file in some way or another. For example this is what I do: I'll take the original Game.exe file and rename it to Game.bak or Game.b~k before dropping the No-CD executable in.
It's good for games like Neverwinter Nights, because when you attempt to patch using a No-CD executable, it won't patch because it'll fail a checksum comparision.