In this blog, I will try to demystify the SQL server logins migration using Data Migration Assistant (DMA)
- DMA assists you to migrate the logins based on a Windows principal (such as a domain user or a Windows domain group) and logins created based on SQL authentication, also called as SQL Server logins.
- DMA currently doesn’t support the logins associated with stand-alone security certificate (logins mapped to certificate), stand-alone asymmetric key (logins mapped to asymmetric key”) and logins map to credentials.
- DMA doesn’t move the “sa” login and server principles with names enclosed by double hash marks (##), which are for internal use only.
- By default, DMA selects all the qualified logins to migrate, but you can pick the specific logins to migrate. When DMA migrates all qualified logins, the login-user mapping remains intact in the databases that are migrated. When you plan to migrate the specific logins, make sure to select the logins that are mapped to one or more users in the databases selected for migration.
- As part of logins migration, DMA also moves, user-defined server roles and add server-level permissions to the user-defined server roles. The owner of the role will be set to “sa” principal.
- As part of the logins migration, DMA assigns the permissions to securables on the target SQL Server as they exist on the source SQL Server. If the login already exists on the target SQL Server, DMA migrates only the permissions assigned to securables, but will not re-create the whole login.
- DMA makes the best effort to map the login to database users if the login already exists on the target server.
- Please review the migration results CSV report to understand the overall status of the logins migration and any post migration actions suggested.