SQL Server migrations, the bain in most DBA's lives when they find out one is coming up. At that top of the list, along with finding out, you need to test your database DR strategy that is fully documented 😉 😉.
Still, one of the most common tasks that can be in day-to-day activity is getting logins created between various SQL Server instances. Say you have to support multiple environments for a given application database, creating that login 3, 4, 5 different times manually using SSMS 😧! dbatools includes a ton of commands that help in migrations.
Do you have multiple Availability Groups (AG) deployed in your environment? AGs that have 3, 4, 6 replicas that you are adding a login to one require adding it to all the other replicas?
These scenarios, among many others, are where Copy-DbaLogin can help make your life easier and give you more time to catch up on other things.
Excluding discussion of a contained database in the context of this post.
To touch an SQL Server instance or databases hosted in SQL Server, you need a login. A login for SQL Server also referred to as a service principal, can either be a Windows Account (local or Active Directory) and a SQL Login. The latter is a "local" account to SQL Server itself. Only the SQL Server instance knows the login name (username) and password. A Windows Account only requires SQL Server to store the unique SID of that account. Windows or Active Directory keep up with the password.
All of the Copy-Dba* commands in dbatools has a basic set of parameters across them all:
The -Source parameter will be the original SQL Server instance you want to copy the login from.
The -Destination parameter will be the target SQL Server instance you want to copy that login to.
Each Source (-SourceSqlCredential) and Destination (-DestinationSqlCredential) parameter has a matching parameter to provide a credential. You use this if the user context the script or PowerShell session is in does not have access or permission to either the Source or Destination.
These can both be a Windows or SQL Login credential.
The Login (s) that you want to copy between the Source and Destination. Notice this is (s), which means the command will handle a single login or multiple.
You can collect a list or array of Logins to provide to this parameter. It also supports piping from Get-DbaLogin. Test your output from Get-DbaLogin to make sure you are grabbing the expected logins, and then pipe those to Copy-DbaLogin 💯.
If you do not provide this parameter, all Logins that can be accessed will be copied to the Destination.
If you do not provide the -Login parameter, this parameter can filter down and exclude logins from being copied. It accepts input as a single or array of logins to exclude.
This parameter is a switch, and when provided, the command will not process all the NT* named logins on the source SQL Server instance.
Will skip the server-level permissions assigned to the Login on the Source instance. Not providing the parameter will process updating permissions on the Destination after the Login is created.
A best practice with SQL Server and security hardening rename the built-in sa account when using SQL Authentication. It is recommended to disable it if your application or environment does not require it. Still, an additional measure is to rename it.
This parameter, a switch, when provided, will copy the rename over to the destination SQL Server instance.
Providing this parameter with a filename path will export the logins on the source SQL Server instance to the SQL file. It will call Export-DbaLogin to perform this action.
A parameter is used for passing in a formal Login object, such as the object returned by Get-DbaLogin.
A unique parameter is used in special situations where you do not want to match the SID for a given Login on the Destination. This parameter will cause a new SID to be generated and applied to the Login on the Destination.
An excellent parameter can be used for scenarios where you are copying between environments or using the Source Login as a base template for creating a new Login. This parameter will accept a hashtable that provides the original Login name to the new Login name you want.
A hashtable in PowerShell is created using the @{} nomenclature. You can do @{'OriginalName' = 'NewName'}. Providing multiple can be done as well @{'OriginalName1' = 'NewName1'; 'OriginalName2' = 'NewName2'}.
Provide this parameter if you need to include the database role membership needs to be included. This would mean the database on the Source instance should already exist on the Destination.
This parameter is used when active sessions on the destination instance can be killed. When you use the -Force parameter, you can overwrite an existing login on the destination instance. If the Login has an active session on the Destination, the Login cannot be dropped and recreated.
No direct actions on the Destination are performed providing this parameter. The command will display the message(s) that describe the effect of the command.
If -Force is not used before dropping a Login, a confirmation prompt is provided. Using -Force will cause the confirm messages to not show, helpful when doing automation or non-interactive processing.
Suppose a Login is found to already exist on the Destination. In that case, this parameter will cause that Login to be dropped (if successful) and then recreate it. Not providing this parameter, when an existing Login is found, it will be skipped for processing.
A standard parameter in dbatools commands, used when you want to utilize try-catch blocks or need the command to formally throw an exception when an error or issue is hit.
A substantial list of examples is included in the comment-based help of the command. You can find those on our documentation site.