Batista's a big one - I know it's not his theme but the staggered pyro with the delayed finale synchronised with his stupid arm lift is cool as all living fuck.
Ezekial Jackson - best addition of lyrics to a crunchy but quite generic existing tune (Brawl For All)
Jeff Jarrett - every theme he's had has been great...but he ain't great. The 'Double J' country theme was fantastic and I still whistle it to this day despite not having heard it in decades. His TNA 'My World' theme was cool as well - it was as cool as Jeff clearly thought he was. Even the short lived 97/98 "NWA" stable theme in the WWF was cool in an ambient sort of way, worked perfectly for the new Midnight Express.
The biggest of all examples though, one that stands head and shoulders above all others is..
The Great Khali! - His theme is bangin' but he's like a wrestling oak tree.