Scottish independence is the SNP leader's great ambition – but his weaknesses could be the project's undoing.