To me, the definition of an "activist judge" is any judge that issues a ruling a person doesn't like. That is, it's all relative. I agree that it's not a bad thing our judges are mostly unelected. This gives them some freedom to interpret what is legal instead of merely what is popular. It changes their focus more towards justice and less towards job preservation.
In Washington State, we elect our Supreme Court justices, which to me has always seemed foolish. This is the Supreme Court of the entire state, and the sole authority for interpreting our state's Constitution. I want them competent much more than I want them popular and able to run a campaign or give a good speech.
I would argue that it does not, but that is, of course, only an opinion.
The way I see it, the courts are not all that free to act. They cannot get out very far ahead of the mainstream of American political thought for fear of losing their credibility.
People who dislike "activist" judges point to things like the ban on school prayer or the Roe decision allowing abortion. But these decisions are popular with large parts of the populace (not necessarily a majority, but close). The courts do not go off making decisions that really fly in the face of public opinion. This is, for example, why the Supreme Court did not rule on the "under God" part of the Pledge of Allegiance. They did not want to look bad.
For a discussion of why this is, you could look at Gerald Rosenberg's book The Hollow Hope.