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:
- Restore databases on new server
- 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)
- 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.