First, I must say that I don't know why these exist in an undocumented form. They have been around for a long time and a search on their names gets me back pages of hits. Being undocumented means that their actual implementation may change slightly from one version of SQL Server to another, mainly because the password hash format could change - and it has changed for the last three major versions of SQL Server. They could also disappear entirely in a future release. So, you shouldn't write applications relying on these functions, but they may come in handy for ad-hoc queries, when you know they're available.

So here's a short description of the two functions:

pwdencrypt: takes a varchar value as parameter and returns a varbin value that is the SQL Server password hash of the input value. I have seen people talking about the use of this function for hashing passwords when implementing custom application authentication. Do not use this! Starting with SQL Server 2005, you can use instead the HashBytes function, to implement any custom hashing scheme that you want. HashBytes provides you with direct access to several hashing algorithms, so there is no point in using pwdencrypt. In fact, I can't come up with any useful need for pwdencrypt and the only reason I included it here is to warn you against using it.

pwdcompare: takes two arguments - a varchar value that is a cleartext password and a varbin value that is a SQL Server password hash, and returns 1 if they match and 0 if they don't. There is a third optional parameter that can be set to 1 if the second parameter represents a pre-SQL Server 2000 password hash value. This third parameter will most likely be dropped in a future SQL Server version.

pwdcompare is useful if you are an administrator looking for accounts that have weak passwords. For example, to query for logins that have an empty password, the following queries can be used (first one will work on SQL Server 2000 and the second one uses the SQL Server 2005 new catalogs):

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

Obviously, these queries can be used to search for other weak passwords than empty ones. Does this constitute a threat against the strength of password hashes by allowing a TSQL brute-force attack? Not really, because such an approach would be very slow - it would be much more efficient to attempt such an attack using a compiled program, and even such approach would only have a good chance of success against a short or weak password. So, the pwdcompare function doesn't make an attacker's job easier. Other than helping administrators with checking for weak passwords, I can't think of another use for this function.