title: Extract password from SQL Login
body: <p>I need to write a script to extract SQL login's password and spread it to another Server.</p>
  
  <p>I did this:</p>
  
  <pre><code>Declare @PasswordBinary varbinary(256)
  Set @PasswordBinary = CAST(LOGINPROPERTY( @LoginName, 'PasswordHash' ) AS varbinary (256))
  
  Exec sp_hexadecimal @PasswordBinary, @PasswordStr Out
  </code></pre>
  
  <p>The sp_hexadecimal :</p>
  
  <pre><code>Create PROCEDURE [dbo].[sp_hexadecimal]
      @binvalue varbinary(256),
      @hexvalue varchar (514) OUTPUT
  AS
    DECLARE @charvalue varchar (514)
    DECLARE @i int
    DECLARE @length int
    DECLARE @hexstring char(16)
  
    SELECT @charvalue = '0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH (@binvalue)
    SELECT @hexstring = '0123456789ABCDEF'
  
    WHILE (@i &lt;= @length)
    BEGIN
       DECLARE @tempint int
       DECLARE @firstint int
       DECLARE @secondint int
  
       SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
       SELECT @firstint = FLOOR(@tempint/16)
       SELECT @secondint = @tempint - (@firstint*16)
  
       SELECT @charvalue = @charvalue +
                           SUBSTRING(@hexstring, @firstint+1, 1) +
                           SUBSTRING(@hexstring, @secondint+1, 1)
  
       SELECT @i = @i + 1
    END
  
    SELECT @hexvalue = @charvalue
  </code></pre>
  
  <p>I have a variable </p>
  
  <pre><code>@SQL nvarchar(max) = 'CREATE LOGIN ' + QUOTENAME(@LoginName) + ' With PASSWORD = ' + @PasswordStr + ' HASHED'''
  </code></pre>
  
  <p>which I send it to the stored procedure on a remote server and I execute this variable there:</p>
  
  <pre><code> Exec (@SQL)
  </code></pre>
  
  <p>but I got an error:</p>
  
  <blockquote>
    <p>The @SQL variable is set to : 'Create Login [Name] With password = 0x
    Hashed'</p>
  </blockquote>
  
  <p>Thanks :)</p>