I think it has positive effects, the main one being greater motivation to work on hard problems and see them to completion. It can also have negative effects, such as an unwillingness to do anything that doesn't directly advance one's career, and a tendency to avoid hard problems for quick-win types of problems.
So I guess it depends on how you use it. The stereotypical politician and scientist are each ambitious in a way, but "want to become President" and "want to win a Nobel Prize" are ambitions that manifest themselves somewhat differently. I tend to trust people more if their ambition appears to be somehow tied up with the subject matter rather than purely social status: I'm wary of people whose sole ambition is to become rich/famous/successful, but admire people who are very ambitious when it comes to wanting to revolutionize/advance some field or activity.