I have had the good fortune to work with exactly one good project manager so far. He is good because he is intelligent enough to realize when he doesn't know something, careful with the team he builds, depends on what his team tells him (rather than ignoring it and going with gut instinct), and focused on processes only insofar as they result in a good product (meaning he approaches any "magic bullet" methodologies with skepticism but with the possibility that they might contain one or two good ideas that make sense in the specific circumstances of the project).
That's really a list of what other project managers do wrong, though, than specifically what makes him valuable to the team.