Migrating to a new (upgraded) SQL server

Question

We have a single SQL server with about 60 databases running on Win2012r2 with SQL 2014.

I am in the process of planning a hardware refresh and plan to migrate to WIN2016 with SQL 2017, I understand the basics of migrating to a new server (copy files over, re-attach, test etc).

My question is, we have specific restricted logins setup for each database. The logins are windows ad groups and have specific permissions for their allowed databases. In addition to this we have about 20 SQL logins with the same setup against their databases.

My question is, how can i migrate these logins and databases while maintaining the permissions for specific databases?

Answer 1

I would use Copy-DbaLogin from the dbatools powershell module.

This command migrates the logins from server A to server B.

From their website the example:

Copy-DbaLogin -Source sqlserver2014a -Destination sqlcluster -Force

Copies the logins from sqlserver2014a to sqlcluster and overwrites them if they exist by using the -Force parameter

What does it migrate?

Migrates logins with SIDs, passwords, defaultdb, server roles & securables, database permissions & securables, login attributes (enforce password policy, expiration, etc.)

Same source as the one mentioned above

Answer 2

I think you can use this if you are not familiarized or not allowed to run with Powershell(Dbatools): https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

Some of us are still using it

Executing stored procedure [sp_help_revlogin] on old instance will generate a windows and sql login script with sid that will fix orphan users and users will get the same permission as old instance.