Of the earlier themes, I'd go with 'Thunderball', 'You Only Live Twice' and 'On Her Majesty's Secret Service'. There were two versions of the Sinatra theme released, one was more orchestral, the other more guitar based - that's the one I prefer though I don't think it was the one used in the movie. The only post 90's one I like is 'The World is Not Enough'.
No-one's mentioned 'Never Say Never Again'. Maybe not the greatest movie or song, but it's great cheese to go with your wine.
(Danged if I can remember who sang the theme).