The only time I got drunk was during the drinking contest quest. It didn't really change much except for my dialogue as I started talking gibberish to the quest giver. That quest is pretty good, go to the Bannered Mare and look for a guy called Sam wearing black robes.
Bean_Dizzle posted... Haha cool. Can you do the drinking quest multiple times?
It's a one off quest and I don't know if there's any others like it. I did find a bunch of Nords hanging around a road though and they invited me to drink with them. After I accepted they thanked me and gave me a bottle of wine. I think the wine is related to another quest.