My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

This is an uncommon task but one that does turn up every once in awhile. A SQL login has to be moved from a development server to a test server, test to prod etc. Or maybe a lateral move to a new server. And frequently the DBA doesn’t and/or shouldn’t know the password.

The first thing to realize is that SQL Server uses hash encryption for its passwords. The password is taken and scrambled up using a hash algorithm. Then when a user tries to log in, SQL takes the input and hashes it using the same algorithm and checks to see if the hashed input matches the hashed password. There is no time (to my knowledge) when the password is “un-hashed” and turned back to an understandable string. All of this means that in order to copy the password intact from one server to another we have to get the “hashed” version of the password and create the login with it. Making sure to tell SQL that it is in fact already hashed and there is no reason to do it again. Fortunately there is a way to do this.

The basic command to create a SQL login is

CREATE LOGIN MyLogin WITH PASSWORD = '<strong password>'

By adding the keyword HASHED to the end of the command we can pass in the hashed value in hex rather than a string.

In order to get the hashed version of the password we can use the function LOGINPROPERTY(loginname, ‘PasswordHash’).

Last but not least, to make this as easy as possible, we need to be able to convert the output of LOGINPROPERTY, which in this case is going to be binary, to a string. This is so we can construct our command in one step.

CONVERT(varchar(max), LOGINPROPERTY(MyLogin, 'PasswordHash'),1 )

Using all of this together I wrote the following query to generate the script I needed.

Once it’s run we copy and paste the commands into a query window pointing to the new server, execute, and we are good to go.

EDIT: Quick change to the above queries. The CONVERT to varchar should have a style of 1 not 2. I’m not sure why 2 worked on the server I tested on but from what I’m reading in BOL and tests on other servers the style should be 1.

Also here is a version of the last query that will generate a script to create the new login with the same SID. This way if you are moving a database with a user that is associated with the login you won’t have to “fix” the user to link them back together again. I talked about this in my blog Logins vs Users.