locked
How to change SQL 2000 login after changing Active Directory login name? RRS feed

  • Question

  • What should I do in SQL 2000 if AD user name changed. There is no ALTER LOGIN command in SQL 2000.

    There is a project in our company to change format of AD users from 4-char to 8-char format. The users going to be renamed not re-created, so SID are not going to be changed.

    What should be done on SQL Server level? May I change Syslogins.Name column?

    Should I also take care of SQL Jobs and DTS Packages owner fields? What's else?

    Thank you,

    Vlad

    Friday, February 19, 2010 3:00 PM

Answers

  • Hi Vlad,

    Do not update the system table. Use sp_droplogin and sp_addlogin and add the AD login with new name. Dropping the login and recreating with the new name will not change its SID .

    For jobs and other dependent objects , please change the ownership manually.


    Thanks, Leks
    • Proposed as answer by Lekss Sunday, February 21, 2010 8:23 PM
    • Marked as answer by Xiao-Min Tan – MSFT Monday, March 1, 2010 2:34 AM
    Friday, February 19, 2010 9:51 PM
  • Hi Leks,

    Thanks a lot for the advise. I have tested the solution for one login. It looks ok with a little correction... sp_droplogin didn't work for that login but sp_revokelogin and sp_grantlogin woked perfectly. SID is the same and relations between the login and DB users didn't change.  

    I will generate "revoke-grant"  script for all logins on a test server and try it on Mon.

    Many thanks,

    Vlad
    • Proposed as answer by Lekss Sunday, February 21, 2010 8:23 PM
    • Marked as answer by Xiao-Min Tan – MSFT Monday, March 1, 2010 2:34 AM
    Saturday, February 20, 2010 2:41 PM

All replies

  • Hi Vlad,

    Do not update the system table. Use sp_droplogin and sp_addlogin and add the AD login with new name. Dropping the login and recreating with the new name will not change its SID .

    For jobs and other dependent objects , please change the ownership manually.


    Thanks, Leks
    • Proposed as answer by Lekss Sunday, February 21, 2010 8:23 PM
    • Marked as answer by Xiao-Min Tan – MSFT Monday, March 1, 2010 2:34 AM
    Friday, February 19, 2010 9:51 PM
  • Hi Leks,

    Thanks a lot for the advise. I have tested the solution for one login. It looks ok with a little correction... sp_droplogin didn't work for that login but sp_revokelogin and sp_grantlogin woked perfectly. SID is the same and relations between the login and DB users didn't change.  

    I will generate "revoke-grant"  script for all logins on a test server and try it on Mon.

    Many thanks,

    Vlad
    • Proposed as answer by Lekss Sunday, February 21, 2010 8:23 PM
    • Marked as answer by Xiao-Min Tan – MSFT Monday, March 1, 2010 2:34 AM
    Saturday, February 20, 2010 2:41 PM