There are several known ways that a malicious USB device can compromise your computer:
Autorun. The USB device can contain software that Windows will automatically run when you plug in the USB device, if you have autorun enabled.
Input device emulation. As @TobyS says, the malicious USB dongle might physically look like a small flash storage device (a dongle), but it can still present itself to the computer as a keyboard or a mouse or somesuch. Then the malicious USB device might start inserting malicious key strokes that subvert your computer.
Exploit a vulnerability. The USB device can contain data or files that exploit vulnerabilities in the code that runs on your computer. As @Jeff mentions, this might include exploiting vulnerabilities in the code that displays file icons in your file manager or exploiting vulnerabilities in your anti-virus software when it scans the drive. It might also include exploiting vulnerabilities in the OS filesystem code (I have seen bugs in that code before, which could be exploited by a malicious filesystem image) or a number of other variants. While I don't currently know of any zero-day vulnerabilities with no known fix in the latest Windows OS, there is a lot of code that runs to handle data from a USB device, and you should assume there are probably more vulnerabilities lurking, waiting to be found.
Social engineering. The USB device can contain files that, when you double-click on them, launch malware. The file names and icons might be arranged to look tempting, to entice users to click on them. This has been exploited before, and is very challenging to prevent.
Bottom line: disabling autorun is a good start, and stops the easiest form of attack. For a company that doesn't have especially high security needs, it's probably "good enough". (It is good enough for me in my own personal computing.) But don't think it's enough to be completely safe; autorun stops only the first of the attacks mentioned above, and other attacks remain possible, as sketched above. Because of these risks, the military completely bans all USB drives.