Hash value of sql server login

  • 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

  • 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 - 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.

  • 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 - 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');

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply