Ex-Scottish National party leader propelled his party into government but his referendum bid for independence failed