Going through "Microsoft SQL Server 2012 - 70-462 Course Subtitle: Administering Microsoft SQL Server 2012 Databases" Can you tell me the different ways to transfer sql server logins. I’m migrating from sql 2005 to sql 2016+ with new hardware for three databases with at least one mirrored to a 2nd hardware server. I may setup witness server for auto fail over too. What is best way to check for any configures or settings for migration.
SQL 2005 to 2016 Server migration then add 2nd physical mirrored server with all configs/settings and logins brought over without breaking anything
Have you looked at the PowerShell cmdlet
Copy-SqlLogin? For SQL Server 2005 and above it migrate logins with SID, password, defaultdb, server roles & securables, database permissions & securables, and login attribute. Copy-SqlLogin is now part of a PowerShell module called dbatools.
Here is a script that will install the module:
Here is the source code for the module:
I have not used it myself, but there are several tutorials on the web, such as:
In this example, they migrate two users from SQL 2014 to SQL 2016, but it should work with SQL 2005 as well.
Of course, I would set up a test instance first, to make sure it doesn't break anything.