There seem to be a couple of misconceptions around the SQL Server handling of login passwords. Hopefully, by the end of this post, you will have a much clearer idea about what is going on under the covers. Note that this refers to the passwords of logins used with SQL Authentication - no passwords are handled by SQL Server when Windows Authentication is used.
The first misconception is that SQL Server is storing an encrypted copy of the login password. This is not true. What SQL Server is storing is a hash of the password. A hash differs from encryption because it is not easily reversible - for encryption, there is a simple way of getting back the original text, by knowing the encryption key; for a hash, there is no simple way of getting back the original text from which the hash is derived (unless you actually know that text already ;)). The advantage of using a hash instead of encryption is that no encryption key needs to be protected (also see this previous post). So, how is this hash used then? When a login occurs, the password submitted by the user is hashed and compared to the stored hash - if they match, the password is accepted and the login succeeds. This means that you could login with the wrong password as long as it has the same hash as the original password - finding such a password, however, should be a computationally hard problem (meaning you have much, much better chances of getting rich playing the lottery than by searching for such password) - the strength of this method relies on this. Also, not to be ignored, the strength of this method relies on the password itself being hard to guess.
How can someone attempt to "break" a login password? The main approach is to sequentially try all passwords in a collection built for this purpose (called generically a dictionary), until one of them happens to match. A dictionary can consist of dates, nouns from various languages, words where numbers or special characters are used instead of words, etc. Hence, the main way to protect a password is to choose it such that there is little chance that it might be part of a dictionary. If you need ideas, an Internet search for "how to choose a password" should provide several links with good advice for choosing a good password. Even if you think you have a good method for choosing a password, you should check such links for a reality check - you may find out that the method you are using is not as good as you think.
As a side note, some refer to hashing as "irreversible encryption". I don't agree with this definition and I find the term of "irreversible encryption" to be an oxymoron, because encryption is meant to be reverted if you know the proper decryption key.
The second misconception is that the algorithm used by SQL Server is a home-brewed proprietary algorithm. I'll go into a bit of history later, but to provide a brief answer - this is false: SQL Server has been using SHA1 since the 2000 version and the previous algorithm, used by versions 6.5 and 7.0, was Snefru. I did not had a chance to work with SQL Server versions earlier than 6.5, so my knowledge only goes that far.
And now, here's a brief overview of the hashes used since version 6.5 of SQL Server. In 6.5, passwords were always ASCII, hence the Snefru algorithm was applied to ASCII strings. In 7.0, passwords started being handled as Unicode, hence the Snefru algorithm was always applied to Unicode strings - this is the main difference between 6.5 and 7.0 hashes. In 2000, the algorithm was changed to SHA1, but for backward compatibility, the server stored both a hash of the original password, as well as a hash of that password converted to uppercase, to allow case insensitive passwords. David Litchfield wrote a paper that criticizes this aspect; it is important to note, however, that while the case-insensitive hash was making password cracking easier by reducing the size of the dictionary that should be used, a good password would still survive an attack simply because of the low probability of it being part of the dictionary. Coming back to the history, SQL Server 2005 does away with the case insensitive hash entirely, breaking backward compatibility in favor of security. One other change in SQL Server 2005 is that, with the move towards increased system catalog security, password hashes have only become visible to sysadmins.
There are few opportunities to use a password hash directly. The main actions that can handle password hashes are CREATE LOGIN and, since SP2, ALTER LOGIN, which allow a login password to be set directly in hashed form. Setting passwords in hash form should be avoided unless absolutely necessary, and this should only be necessary in scenarios where an application attempts to keep login passwords in sync across several servers. CREATE LOGIN will accept as input a 7.0, 2000, or 2005 hash, while ALTER LOGIN will only accept a 2000 or 2005 hash. Note that 6.5 hashes are no longer supported in SQL Server 2005. Also, 2000 hashes are immediately converted to the 2005 format by removing the case-insensitive hash and 7.0 hashes will be upgraded to the 2005 format as soon as the server gets to handle the corresponding password - either during a successful login or during a successful password change (when OLD_PASSWORD is specified).