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:
https://gallery.technet.microsoft.com/scriptcenter/Fully-TransferMigrate-SQL-25a0cf05Here is the source code for the module:
https://github.com/sqlcollaborative/dbatoolsI have not used it myself, but there are several tutorials on the web, such as:
https://www.mssqltips.com/sqlservertip/4654/migrate-sql-server-logins-with-powershell/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.
