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.

CREATE LOGIN MyLogin WITH 
PASSWORD = 0x0100230BC24D34869B5E83240FD0202462F384A73E7516D8B50A 
HASHED

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.

 SELECT 'CREATE LOGIN MyLogin WITH PASSWORD = ' +
     CONVERT(varchar(max), LOGINPROPERTY('MyLogin', 'PasswordHash'),1 ) + 
     ' HASHED'

And this one in case I want to generate more than one command at once.

SELECT 'CREATE LOGIN '+name+ ' WITH PASSWORD = ' +
     CONVERT(varchar(max), LOGINPROPERTY(name, 'PasswordHash'),1 ) + 
     ' HASHED'
FROM sys.server_principals
WHERE name in ('MyLogin','MyLogin2')
  AND type = 'S'

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.

SELECT 'CREATE LOGIN '+name+ ' WITH PASSWORD = ' +
     CONVERT(varchar(max), LOGINPROPERTY(name, 'PasswordHash'),1 ) + 
     ' HASHED, SID = ' + CONVERT(varchar(max), sid, 1)
FROM sys.server_principals
WHERE name in ('MyLogin','MyLogin2')
  AND type = 'S'

Filed under: Microsoft SQL Server, Security, SQLServerPedia Syndication, T-SQL Tagged: language sql, microsoft sql server, security, server permissions, sql statements, system functions, T-SQL