How to handle logins and database users for migration of sql server into new domain (new installation, no trust with original domain)?



  • I want to migrate SQL server 2016 to SQL server 2019 (both machines are onprem, separate domain, no trust/connectivity between the domains, the new sql server will be a new installation on target machine).

    I have scripted the server logins (sql logins, windows logins, windows groups), roles and membership. My plan to restore full backup of the source databases with no recovery, followed by subsequent differential or log backups with recovery. Then deploy the server logins, roles and membership on the target server (this will bring across the windows and sql logins).

    My question is about the sql server logins (windows users, groups and sql logins) and database users (mapping to the logins). I understand that logins has a sid, and the database user should match with this sid to establish the mapping between database user and server level login. For sql logins I think there should not be a problem but for windows login, since the domain is different, how do I handle the sid for windows logins (and windows groups) and database users?

    Since the source windows user's (and group's) domain doesn't exist on the target server, so what do I need to do to make the windows logins (and groups) to work? Should I be bringing the old windows logins into the new sql server and then renaming them or is the following more appropriate:

    1. Restore databases on new server
    2. Script the source logins, roles, securable permissions and run it on the target with the domain name updated to new domain name (example: domainold\user1 to be modified to domainnew\user1)
    3. SQL user logins will automatically get mapped as the sid will be present in the above script. But for windows logins and groups, run following command to establish the sid mapping:

    --rename and map database windows logins and groups to new domain login

    ALTER USER [domainold\user1] WITH NAME = [domainnew\user1], LOGIN=[domainnew\user1]
    ALTER USER [domainold\group1] WITH NAME = [domainnew\group1], LOGIN=[domainnew\group1]
    

    Please correct me.



  • There is a very well documented method by Microsoft:

    https://docs.microsoft.com/en-us/troubleshoot/sql/security/transfer-logins-passwords-between-instances

    The steps are too easy. To Transfer logins from Server A to Server B:

    1. Execute the CEATE PROCEDURE script (Script exists in link) on Server A

    2. In the SSMS query editor, select the Results to Text option.

    3. Run the following statement in the same or a new query window:

      EXEC sp_help_revlogin

    The output script that the sp_help_revlogin stored procedure generates is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.

    1. Run the output of step 3 in Server B



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2