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.

1 Answer 1


There is a very well documented method by Microsoft:

Transfer logins and passwords between instances of SQL Server

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
  • But the windows logins wont work on server B because there is no trust between domains
    – variable
    Apr 12, 2022 at 5:34
  • @variable You are right. It won't. But when the script becomes ready replace the old domain name in the script with the new one. It works when the DC structures are the same. Apr 12, 2022 at 9:59
  • That was my question - how to handle this situation, can you re-read my question please
    – variable
    Apr 12, 2022 at 10:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.