locked
Script logins from SQL 7 to 2000, trouble with sp_help_revlogin RRS feed

  • Question

  • I'm trying to retire some SQL Server 7 SP4 servers and script out all SQL logins with the correct name, password, and sid. I have used sp_help_revlogin with success on SQL 2000 and up but it doesn't seem to work for me when going from SQL 7 to 2000.

    Here is what I have tried so far:
    On the SQL 7 source box, created sp_help_revlogin fresh from MSKB 246133 then created a new user named "tomw" with password "Abc1234".

    Output from sp_help_revlogin 'tomw'  results in this:

    DECLARE @pwd sysname 
    -- Login: tomw
    SET @pwd = CONVERT (varbinary(256), 0x2131214A21212334544446324128302C)
    EXEC master..sp_addlogin 'tomw', @pwd, @sid = 0x0C4D5E2BB066C04BA3CD83A31A7440EC, @encryptopt = 'skip_encryption'
    

    This script works fine when run on another SQL 7 server, but after running on a SQL 2000 (SP4) server I get a password mismatch.

    After running the script on the destination SQL 2000 server I have assigned the login to the public role in the master db. I have tried typing the password in all lowercase / all uppercase. I also tried changing the script parameter @encryptopt to 'skip_encryption_old'.

    I noticed that the hex value is short (only 32 chars after the 0x) as compared to the output when this is run on a SQL 2000 server (in which case the hex value has 128 chars after the 0x), so I suspect something with the password encryption.

    Has anyone encountered this issue or at least have any thoughts on how to resolve it?

    Friday, May 27, 2011 2:34 PM

Answers

  • Hi Tom,

    Your assumption with password encryption is right. I guess you could try 'DTS Package Transfer Logins Task in SQL Server 2000' in KB 246133 .

    Hope this helps.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Peja Tao Friday, June 3, 2011 1:00 AM
    Monday, May 30, 2011 4:44 AM

All replies

  • SQL Server 2000 introduced a new system for hashing the password. I haven't looked into this in a while but I think you might need the skip_encryption_old option described here: http://msdn.microsoft.com/en-us/library/ms173768.aspx
    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Friday, May 27, 2011 3:32 PM
  • Sorry, I now see you already tried that.
    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Friday, May 27, 2011 3:33 PM
  • Hi Tom,

    Your assumption with password encryption is right. I guess you could try 'DTS Package Transfer Logins Task in SQL Server 2000' in KB 246133 .

    Hope this helps.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Peja Tao Friday, June 3, 2011 1:00 AM
    Monday, May 30, 2011 4:44 AM