JJ writes "This question is regarding a question answered by Scott Mitchell (http://www.4guysfromrolla.com/webtech/sqlguru/q051500-3.shtml ). Please refer to the above url for that article.As Scott pointed out the whole point of the SQL Users password is to allow/disallow access to the server. But he also said "The password in syslogins is encrypted, so there really is no way you can get to it". The two can not be both correct at the same time. To allow/disallow access, you have to have a way to get the passwords from the sysxlogin table and compare it to the password entered by a user to validate the login.The passwords store in sysxlogin table is encrypted using a query convert(binary(256), pwdencrypt(@pwd))
you can find the detail of this if you open stored procedure sp_password. There must be a decrypt fuction to work with pwddecrypt() to decrypt the password, because it is not reliable if you compare values at encrypted format in sql. In another words, if you encrypt a password using update... set password=convert(binary(256), pwdencrypt('password')) then you want to view the result using select * from ... where password=convert(binary(256), pwdencrypt('password')) you will not get what you expected, I tested this on SQL Server 2000. In one table I have a column store sensitive information. I made the column data type as varbinary and encrypted the data with pwdencrypt(), but can not have it decrypted. Did any one know the decrypt function for pwdencrypt()? Thanks."
validate a SQL Users password 
Posted - 2001-10-30 : 18:12:07