I was skeptical at first, but now I use Dynamic Data almost as much as I do "standard" ASP.NET sites. Out of the box, it's pretty generic, but it's customizable, and you can include standard ASP.NET pages in it.
At first, I would use it as a separate Admin site when I needed a "back door" into the data from a "standard" app. Lately, however, my approach has been to do some more planning, and decide which of the tables I would like users to access via the Dynamic Data mechanisms, and which data I want more fine control over. You can scaffold only the table you want, and this works good for "lookup" tables where you want an end user to be able to add/delete. An example would be in our email coupon program, where customers can sign up to receive coupons via email. They can choose their coupon categories - hot foods, beverages, gas, produce, etc. The administrator of the overall coupon program needs to be able to add and remove categories, and Dynamic Data is WONDERFUL for this sort of thing.
Dynamic data takes care of the data validation (a huge plus for security AND usability), mapping our relationships (a HUGE time saver) and just "does it right". In the business environment, security and productivity are two very real concerns that are handled poorly by most developers, and Dynamic Data seems to handle the basics well.
So yes, I do think it's worth it. It's very powerful and an excellent tool to have in your toolbox, but one that should be wielded with skill, which takes time and practice. And it should not be the only tool in your toolbox.