< Back
Copy-DbaLogin
Post
NAME Copy-DbaLogin
SYNOPSIS
Migrates logins from source to destination SQL Servers. Supports SQL Server versions 2000 and newer.
SYNTAX
Copy-DbaLogin [-SourceSqlCredential <Pscredential>] [-DestinationSqlCredential <Pscredential>] [-Login
<System.Object[]>] [-ExcludeLogin <System.Object[]>] [-ExcludeSystemLogins <Switch>] [-LoginRenameHashtable
<Hashtable>] [-KillActiveConnection <Switch>] [-Force <Switch>] [-ExcludePermissionSync <Switch>]
[-EnableException <Switch>] [<CommonParameters>]
Copy-DbaLogin -Source <DbaInstanceParameter> [-SourceSqlCredential <Pscredential>] -Destination
<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [-DestinationSqlCredential <Pscredential>] [-Login
<System.Object[]>] [-ExcludeLogin <System.Object[]>] [-ExcludeSystemLogins <Switch>] [-SyncSaName <Switch>]
[-LoginRenameHashtable <Hashtable>] [-KillActiveConnection <Switch>] [-Force <Switch>] [-ExcludePermissionSync
<Switch>] [-EnableException <Switch>] [<CommonParameters>]
Copy-DbaLogin -Source <DbaInstanceParameter> [-SourceSqlCredential <Pscredential>] [-DestinationSqlCredential
<Pscredential>] [-Login <System.Object[]>] [-ExcludeLogin <System.Object[]>] [-ExcludeSystemLogins <Switch>]
-OutFile <String> [-LoginRenameHashtable <Hashtable>] [-KillActiveConnection <Switch>] [-Force <Switch>]
[-ExcludePermissionSync <Switch>] [-EnableException <Switch>] [<CommonParameters>]
Copy-DbaLogin [-SourceSqlCredential <Pscredential>] -Destination
<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [-DestinationSqlCredential <Pscredential>] [-Login
<System.Object[]>] [-ExcludeLogin <System.Object[]>] [-ExcludeSystemLogins <Switch>] [-InputObject
<System.Object>] [-LoginRenameHashtable <Hashtable>] [-KillActiveConnection <Switch>] [-Force <Switch>]
[-ExcludePermissionSync <Switch>] [-EnableException <Switch>] [<CommonParameters>]
Copy-DbaLogin [-SourceSqlCredential <Pscredential>] [-DestinationSqlCredential <Pscredential>] [-Login
<System.Object[]>] [-ExcludeLogin <System.Object[]>] [-ExcludeSystemLogins <Switch>] [-SyncSaName <Switch>]
[-LoginRenameHashtable <Hashtable>] [-KillActiveConnection <Switch>] [-Force <Switch>] [-ExcludePermissionSync
<Switch>] [-EnableException <Switch>] [<CommonParameters>]
DESCRIPTION
SQL Server 2000: Migrates logins with SIDs, passwords, server roles and database roles.
SQL Server 2005 & newer: Migrates logins with SIDs, passwords, defaultdb, server roles & securables, database
permissions & securables, login attributes (enforce password policy, expiration, etc.)
The login hash algorithm changed in SQL Server 2012, and is not backwards compatible with previous SQL Server
versions. This means that while SQL Server 2000 logins can be migrated to SQL Server 2012, logins created in SQL
Server 2012 can only be migrated to SQL Server 2012 and above.
PARAMETERS
-Destination [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
Destination SQL Server. You must have sysadmin access and the server must be SQL Server 2000 or higher.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DestinationSqlCredential [<Pscredential>]
Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory -
Integrated are all supported.
For MFA support, please use Connect-DbaInstance.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables
advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own
try/catch.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeLogin [<System.Object[]>]
The login(s) to exclude. Options for this list are auto-populated from the server.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludePermissionSync [<Switch>]
Skips permission syncs
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeSystemLogins [<Switch>]
If this switch is enabled, NT SERVICE accounts will be skipped.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Force [<Switch>]
If this switch is enabled, the Login(s) will be dropped and recreated on Destination. Logins that own Agent
jobs cannot be dropped at this time.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-InputObject [<System.Object>]
Takes the parameters required from a Login object that has been piped into the command
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-KillActiveConnection [<Switch>]
If this switch and -Force are enabled, all active connections and sessions on Destination will be killed.
A login cannot be dropped when it has active connections on the instance.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Login [<System.Object[]>]
The login(s) to process. Options for this list are auto-populated from the server. If unspecified, all logins
will be processed.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-LoginRenameHashtable [<Hashtable>]
Pass a hash table into this parameter to be passed into Rename-DbaLogin to update the Login and mappings after
the Login is completed.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-OutFile [<String>]
Calls Export-DbaLogin and exports all logins to a T-SQL formatted file. This does not perform a copy, so no
destination is required.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Source [<DbaInstanceParameter>]
Source SQL Server. You must have sysadmin access and server version must be SQL Server version 2000 or higher.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SourceSqlCredential [<Pscredential>]
Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory -
Integrated are all supported.
For MFA support, please use Connect-DbaInstance.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SyncSaName [<Switch>]
If this switch is enabled, the name of the sa account will be synced between Source and Destination
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: Migration, Login
Author: Chrissy LeMaire (@cl), netnerds.net
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
Requires: sysadmin access on SQL Servers
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Copy-DbaLogin -Source sqlserver2014a -Destination sqlcluster -Force
Copies all logins from Source Destination. If a SQL Login on Source exists on the Destination, the Login on
Destination will be dropped and recreated.
If active connections are found for a login, the copy of that Login will fail as it cannot be dropped.
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Copy-DbaLogin -Source sqlserver2014a -Destination sqlcluster -Force -KillActiveConnection
Copies all logins from Source Destination. If a SQL Login on Source exists on the Destination, the Login on
Destination will be dropped and recreated.
If any active connections are found they will be killed.
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Copy-DbaLogin -Source sqlserver2014a -Destination sqlcluster -ExcludeLogin realcajun -SourceSqlCredential
$scred -DestinationSqlCredential $dcred
Copies all Logins from Source to Destination except for realcajun using SQL Authentication to connect to both
instances.
If a Login already exists on the destination, it will not be migrated.
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>Copy-DbaLogin -Source sqlserver2014a -Destination sqlcluster -Login realcajun, netnerds -force
Copies ONLY Logins netnerds and realcajun. If Login realcajun or netnerds exists on Destination, the existing
Login(s) will be dropped and recreated.
-------------------------- EXAMPLE 5 --------------------------
PS C:\\>Copy-DbaLogin -LoginRenameHashtable @{ "PreviousUser" = "newlogin" } -Source $Sql01 -Destination Localhost
-SourceSqlCredential $sqlcred -Login PreviousUser
Copies PreviousUser and then renames it to newlogin.
-------------------------- EXAMPLE 6 --------------------------
PS C:\\>Get-DbaLogin -SqlInstance sql2016 | Out-GridView -Passthru | Copy-DbaLogin -Destination sql2017
Displays all available logins on sql2016 in a grid view, then copies all selected logins to sql2017.
-------------------------- EXAMPLE 7 --------------------------
PS C:\\>$loginSplat = @{
>> Source = $Sql01
>> Destination = "Localhost"
>> SourceSqlCredential = $sqlcred
>> Login = 'ReadUserP', 'ReadWriteUserP', 'AdminP'
>> LoginRenameHashtable = @{
>> "ReadUserP" = "ReadUserT"
>> "ReadWriteUserP" = "ReadWriteUserT"
>> "AdminP" = "AdminT"
>> }
>> }
PS C:\\> Copy-DbaLogin @loginSplat
Copies the three specified logins to 'localhost' and renames them according to the LoginRenameHashTable.
RELATED LINKS
https://dbatools.io/Copy-DbaLogin
SYNOPSIS
Migrates logins from source to destination SQL Servers. Supports SQL Server versions 2000 and newer.
SYNTAX
Copy-DbaLogin [-SourceSqlCredential <Pscredential>] [-DestinationSqlCredential <Pscredential>] [-Login
<System.Object[]>] [-ExcludeLogin <System.Object[]>] [-ExcludeSystemLogins <Switch>] [-LoginRenameHashtable
<Hashtable>] [-KillActiveConnection <Switch>] [-Force <Switch>] [-ExcludePermissionSync <Switch>]
[-EnableException <Switch>] [<CommonParameters>]
Copy-DbaLogin -Source <DbaInstanceParameter> [-SourceSqlCredential <Pscredential>] -Destination
<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [-DestinationSqlCredential <Pscredential>] [-Login
<System.Object[]>] [-ExcludeLogin <System.Object[]>] [-ExcludeSystemLogins <Switch>] [-SyncSaName <Switch>]
[-LoginRenameHashtable <Hashtable>] [-KillActiveConnection <Switch>] [-Force <Switch>] [-ExcludePermissionSync
<Switch>] [-EnableException <Switch>] [<CommonParameters>]
Copy-DbaLogin -Source <DbaInstanceParameter> [-SourceSqlCredential <Pscredential>] [-DestinationSqlCredential
<Pscredential>] [-Login <System.Object[]>] [-ExcludeLogin <System.Object[]>] [-ExcludeSystemLogins <Switch>]
-OutFile <String> [-LoginRenameHashtable <Hashtable>] [-KillActiveConnection <Switch>] [-Force <Switch>]
[-ExcludePermissionSync <Switch>] [-EnableException <Switch>] [<CommonParameters>]
Copy-DbaLogin [-SourceSqlCredential <Pscredential>] -Destination
<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [-DestinationSqlCredential <Pscredential>] [-Login
<System.Object[]>] [-ExcludeLogin <System.Object[]>] [-ExcludeSystemLogins <Switch>] [-InputObject
<System.Object>] [-LoginRenameHashtable <Hashtable>] [-KillActiveConnection <Switch>] [-Force <Switch>]
[-ExcludePermissionSync <Switch>] [-EnableException <Switch>] [<CommonParameters>]
Copy-DbaLogin [-SourceSqlCredential <Pscredential>] [-DestinationSqlCredential <Pscredential>] [-Login
<System.Object[]>] [-ExcludeLogin <System.Object[]>] [-ExcludeSystemLogins <Switch>] [-SyncSaName <Switch>]
[-LoginRenameHashtable <Hashtable>] [-KillActiveConnection <Switch>] [-Force <Switch>] [-ExcludePermissionSync
<Switch>] [-EnableException <Switch>] [<CommonParameters>]
DESCRIPTION
SQL Server 2000: Migrates logins with SIDs, passwords, server roles and database roles.
SQL Server 2005 & newer: Migrates logins with SIDs, passwords, defaultdb, server roles & securables, database
permissions & securables, login attributes (enforce password policy, expiration, etc.)
The login hash algorithm changed in SQL Server 2012, and is not backwards compatible with previous SQL Server
versions. This means that while SQL Server 2000 logins can be migrated to SQL Server 2012, logins created in SQL
Server 2012 can only be migrated to SQL Server 2012 and above.
PARAMETERS
-Destination [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
Destination SQL Server. You must have sysadmin access and the server must be SQL Server 2000 or higher.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DestinationSqlCredential [<Pscredential>]
Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory -
Integrated are all supported.
For MFA support, please use Connect-DbaInstance.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables
advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own
try/catch.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeLogin [<System.Object[]>]
The login(s) to exclude. Options for this list are auto-populated from the server.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludePermissionSync [<Switch>]
Skips permission syncs
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeSystemLogins [<Switch>]
If this switch is enabled, NT SERVICE accounts will be skipped.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Force [<Switch>]
If this switch is enabled, the Login(s) will be dropped and recreated on Destination. Logins that own Agent
jobs cannot be dropped at this time.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-InputObject [<System.Object>]
Takes the parameters required from a Login object that has been piped into the command
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-KillActiveConnection [<Switch>]
If this switch and -Force are enabled, all active connections and sessions on Destination will be killed.
A login cannot be dropped when it has active connections on the instance.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Login [<System.Object[]>]
The login(s) to process. Options for this list are auto-populated from the server. If unspecified, all logins
will be processed.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-LoginRenameHashtable [<Hashtable>]
Pass a hash table into this parameter to be passed into Rename-DbaLogin to update the Login and mappings after
the Login is completed.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-OutFile [<String>]
Calls Export-DbaLogin and exports all logins to a T-SQL formatted file. This does not perform a copy, so no
destination is required.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Source [<DbaInstanceParameter>]
Source SQL Server. You must have sysadmin access and server version must be SQL Server version 2000 or higher.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SourceSqlCredential [<Pscredential>]
Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory -
Integrated are all supported.
For MFA support, please use Connect-DbaInstance.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SyncSaName [<Switch>]
If this switch is enabled, the name of the sa account will be synced between Source and Destination
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: Migration, Login
Author: Chrissy LeMaire (@cl), netnerds.net
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
Requires: sysadmin access on SQL Servers
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Copy-DbaLogin -Source sqlserver2014a -Destination sqlcluster -Force
Copies all logins from Source Destination. If a SQL Login on Source exists on the Destination, the Login on
Destination will be dropped and recreated.
If active connections are found for a login, the copy of that Login will fail as it cannot be dropped.
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Copy-DbaLogin -Source sqlserver2014a -Destination sqlcluster -Force -KillActiveConnection
Copies all logins from Source Destination. If a SQL Login on Source exists on the Destination, the Login on
Destination will be dropped and recreated.
If any active connections are found they will be killed.
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Copy-DbaLogin -Source sqlserver2014a -Destination sqlcluster -ExcludeLogin realcajun -SourceSqlCredential
$scred -DestinationSqlCredential $dcred
Copies all Logins from Source to Destination except for realcajun using SQL Authentication to connect to both
instances.
If a Login already exists on the destination, it will not be migrated.
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>Copy-DbaLogin -Source sqlserver2014a -Destination sqlcluster -Login realcajun, netnerds -force
Copies ONLY Logins netnerds and realcajun. If Login realcajun or netnerds exists on Destination, the existing
Login(s) will be dropped and recreated.
-------------------------- EXAMPLE 5 --------------------------
PS C:\\>Copy-DbaLogin -LoginRenameHashtable @{ "PreviousUser" = "newlogin" } -Source $Sql01 -Destination Localhost
-SourceSqlCredential $sqlcred -Login PreviousUser
Copies PreviousUser and then renames it to newlogin.
-------------------------- EXAMPLE 6 --------------------------
PS C:\\>Get-DbaLogin -SqlInstance sql2016 | Out-GridView -Passthru | Copy-DbaLogin -Destination sql2017
Displays all available logins on sql2016 in a grid view, then copies all selected logins to sql2017.
-------------------------- EXAMPLE 7 --------------------------
PS C:\\>$loginSplat = @{
>> Source = $Sql01
>> Destination = "Localhost"
>> SourceSqlCredential = $sqlcred
>> Login = 'ReadUserP', 'ReadWriteUserP', 'AdminP'
>> LoginRenameHashtable = @{
>> "ReadUserP" = "ReadUserT"
>> "ReadWriteUserP" = "ReadWriteUserT"
>> "AdminP" = "AdminT"
>> }
>> }
PS C:\\> Copy-DbaLogin @loginSplat
Copies the three specified logins to 'localhost' and renames them according to the LoginRenameHashTable.
RELATED LINKS
https://dbatools.io/Copy-DbaLogin