Just like anything else, if it's done correctly, it can be great. The problem is most people don't bother to spend the time at it and throw out a half-assed system. To date, Fallout 3 is the only one where the scaling isn't horribly done.
The first time I played through Bioshock (PC) I had adaptive difficulty turned on. About 1/2 way through the game, I began wondering why every enemy I came across took 5 or 6 headshots with anti-personnel rounds to bring down. Unfortunately, it took them a while to patch it so that the difficulty would turn itself back down if you weren't doing as well.
Oblivion was horribly done. Enemies would scale up as high as your level, but your ability to scale up your damage was often cut short long before that. A level gained due to speechcraft and alchemy would net the same increase in monster stats as a level in blades and repair. In the end I wound up editing the game via the construction set just so I could actually enjoy it again.
Ultimately, companies need to hire players rather than play testers. Someone who's going to go through a game because they enjoy it will find many more problems than someone who's told specifically what to test for and look at.