My good friend Brady Gaster, once again, comes through with the best tip of today. A SQL guru he works with tipped him to this and he shared with me (just so proper credit gets served).

Really pretty simple actually. Paste this into Query Analyzer.

select pwdencrypt('Scott Cate')
select pwdcompare('Scott Cate',pwdencrypt('Scott Cate')

I found this article online about a buffer overrun error, but it's pretty old and seems to have been fixed with SP3. They claimed a buffer overrun with this call.

SELECT pwdencrypt(REPLICATE('A',353))

I tried it in SP3 and it fails because the parameter is invalid, not because of an overrun.

Thanks Brady.

Warning/Update: Since I read about and wrote this, I've been sent a couple of links; informing me that this is not a good idea to use. First of all, since it's undocumented the hashing algorithms inside SQL server might change, rendering your pwdCompare() function useless. Also the hashed code itself can be broken pretty easily. Thanks Tim Heuer for this link, explaining the vulnerability.