Most trainers have never failed anyone on a CSM course, and not all CST (Certified Scrum Trainers) are good at getting useful learning across. For this reason, I don't put much faith myself in the certificates.
Unfortunately, managers in the industry do, so the certificate is valuable. If you can find someone who's recommended, or someone who's actually failed people on the course, then they're probably better than average.
Once you've got your CSM, be careful that you don't end up on a project where you're solely responsible for the Agile process with no support. A CSM is not enough. You'll need either some experienced Agilists or a coach around to help you.
Declaration of interest: I'm an Agile coach and make pretty good money sorting out the mess for people who've relied on CSMs.
**Edit:** Since answering this question, the Scrum Alliance has adopted an exam to go with the course, so it's actually possible to fail.