In terms of a non-Goldeneye Bond game, this is probably my favorite.

Probably the closest a game a got to making you actually feel like Bond with all the sexiness and gadgets and "007 moments".

I had the PS2 version.