Good - I know the senior executive, who is the project sponsor, by name. I have confidence in that individual. For example, the VP of Production is the project sponsor of the ERP implementation.
Bad - The role is diffused among multiple individuals. I'm not sure who has been assigned to do what. I'm not sure why there are multiple project sponsors. For example, the VP of Production and the VP Finance are both project sponsors of the ERP implementation; both seem to defer to the other on decisions.
Ugly - There is no project sponsor or I don't know who holds the role of project sponsor. Sometimes organizations confuse the role of project sponsor and project manager. Sometimes organizations believe the two roles are synonymous. For example, the VP who was project sponsor was headhunted and is now gone; no replacement has been named even though a manager was promoted to the VP spot.
Recommend appointment of the manager of the business area that has most to gain from the project as project sponsor. This person becomes chairman of the project steering committee. For example, the VP of Production is a good choice for project sponsor of an ERP implementation. Because ERP typically crosses organizational boundaries, include other VP's on the steering committee.
Provide some orientation to the person to become effective in the role. The role is to address major issues and support the project team, especially the project manager. The role is not to be a figurehead for some overly ambitious project manager. The role is not to become the scapegoat for some looming disaster.
Successful project managers work to build and maintain the relationship with the project sponsor.