Everything you ever wanted to know about passwords and then some

Have you ever tried to make sense of how passwords are stored in SQL Server 2005?  It used to be a bit easier in SQL Server 2000.  Back then, the sysxlogins virtual table had a column called password which contained a hashed value of the SQL accounts’ passwords.  Windows’ accounts passwords were always NULL.  Going back to even earlier versions, one of the shameful secrets of SQL Server was that passwords were stored in a clear text file in the SQL Server directory.

Now, in SQL Server 2005, the same information is available from the system function LOGINPROPERTY with a property name of ‘PasswordHash’ as described here:

http://msdn2.microsoft.com/en-us/library/ms178593.aspx

and here:

http://msdn2.microsoft.com/en-us/library/ms345412.aspx.

In addition, here’s a great KB support article that explains how to move passwords between SQL Servers, even if they are different versions:

http://support.microsoft.com/default.aspx/kb/246133

Enjoy,

-Kevin