There are sex jokes, F-bombs and weary cultural references – but when the former Late Late Show host tells a story about the Rolling Stones, the show comes alive