Hash value of sql server login

• caz100

  SSCommitted

  Points: 1827

  More actions

  October 5, 2017 at 10:08 am

  #402569

  Hi 

  I have passwords I need to change on various sql server versions...2005 upwards to 2012

  It would be useful to be able to alter the password back to the old value if needs be but I do not know that current password. 

  Can anyone tell me how to get the present hash value for a login and then how to change the password back to the old value using that hashed value if necessary? 

  Thanks in Advance

• Sreekanth B

  SSCertifiable

  Points: 6145

  More actions

  October 5, 2017 at 10:15 am

  #1962512

  you can script out existing logins with hashed pwds using sp_helprevlogin. or below script should be helpful...
  SELECT 'IF(SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL)BEGIN CREATE LOGIN '+QUOTENAME(SP.name)+
   CASE WHEN SP.type_desc = 'SQL_LOGIN'
    THEN ' WITH PASSWORD = '+CONVERT(NVARCHAR(MAX),SL.password_hash,1)+' HASHED'+ ',SID='+CONVERT(NVARCHAR(MAX),SL.sid,1)
    ELSE ' FROM WINDOWS'
   END + ';/*'+SP.type_desc+'*/ END;'
   COLLATE SQL_Latin1_General_CP1_CI_AS
FROM sys.server_principals AS SP
LEFT JOIN sys.sql_logins AS SL
  ON SP.principal_id = SL.principal_id
WHERE SP.type_desc IN ('SQL_LOGIN','WINDOWS_GROUP','WINDOWS_LOGIN')
 AND SP.name NOT LIKE '##%##'
 AND SP.name NOT IN ('SA');use results to text and save the output to a file. You might want to tweak code a little per your requirements as needed.

• Sreekanth B

  SSCertifiable

  Points: 6145

  More actions

  October 5, 2017 at 10:23 am

  #1962513

  Sreekanth B - Thursday, October 5, 2017 10:15 AM

  you can script out existing logins with hashed pwds using sp_helprevlogin. or below script should be helpful...
  SELECT 'IF(SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL)BEGIN CREATE LOGIN '+QUOTENAME(SP.name)+
   CASE WHEN SP.type_desc = 'SQL_LOGIN'
    THEN ' WITH PASSWORD = '+CONVERT(NVARCHAR(MAX),SL.password_hash,1)+' HASHED'+ ',SID='+CONVERT(NVARCHAR(MAX),SL.sid,1)
    ELSE ' FROM WINDOWS'
   END + ';/*'+SP.type_desc+'*/ END;'
   COLLATE SQL_Latin1_General_CP1_CI_AS
FROM sys.server_principals AS SP
LEFT JOIN sys.sql_logins AS SL
  ON SP.principal_id = SL.principal_id
WHERE SP.type_desc IN ('SQL_LOGIN','WINDOWS_GROUP','WINDOWS_LOGIN')
 AND SP.name NOT LIKE '##%##'
 AND SP.name NOT IN ('SA');use results to text and save the output to a file. You might want to tweak code a little per your requirements as needed.

  Quick Tip: If you decide to save results to text, make sure to change query results under options in SSMS, setting "maximum number of characters displayed in each column" to 8192. If not, the result set might get chopped off and you get incorrect SIDs in your scripts.

• caz100

  SSCommitted

  Points: 1827

  More actions

  October 6, 2017 at 3:50 am

  #1962585

  @ Sreekanth B - thanks for that. 

  Any idea then how to alter the password back to the old password using this Hash value ?

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  October 6, 2017 at 3:52 am

  #1962586

  https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-login-transact-sql
  Example G

  John

• caz100

  SSCommitted

  Points: 1827

  More actions

  October 6, 2017 at 4:21 am

  #1962587

  @John Mitchell-245523 - thanks a mill 

  So to combine something like this will work nicely:

  SELECT 'ALTER LOGIN '+QUOTENAME(SP.name)+
   CASE WHEN SP.type_desc = 'SQL_LOGIN'
    THEN ' WITH PASSWORD = '+CONVERT(NVARCHAR(MAX),SL.password_hash,1)+' HASHED'+ ',SID='+CONVERT(NVARCHAR(MAX),SL.sid,1)
    ELSE ' FROM WINDOWS'
   END + ';/*'+SP.type_desc+'*/ END;'
   COLLATE SQL_Latin1_General_CP1_CI_AS
  FROM sys.server_principals AS SP
  LEFT JOIN sys.sql_logins AS SL
  ON SP.principal_id = SL.principal_id
  WHERE SP.type_desc IN ('SQL_LOGIN')
  AND SP.name NOT LIKE '##%##';
  --AND SP.name NOT IN ('SA');