SQL login passwords and Pwdcompare

I'll spare the details but after a a little investigation, the HASHBYTES function cannot be used to generate a SQL login password. There are 2 undocumented functions pwdencrypt and pwdcompare that Management Studio uses for this.

If you were really interested in how to form the passwords, then the login migration script here will give more insight.

Link : How to transfer the logins and the passwords between instances of SQL Server 2005/8

The use of pwdcompare can be demonstrated as follows -

If I was daft enough to use my cat's name as a password (I'm not), the following query would return the username I had used the password with.

select name from sys.syslogins where pwdcompare('coco', password) = 1

Links:
cannot engineer sql password hash using HASHBYTES, have to use the undocumented stored procedure 'pwdencrypt'
SQL Server undocumented password hashing builtins: pwdcompare and pwdencrypt