commands

Clear
^

Copy-DbaLogin
Author Chrissy LeMaire (@cl), netnerds.net
Availability Windows, Linux, macOS

 

Want to see the source code for this command? Check out Copy-DbaLogin on GitHub.
Want to see the Bill Of Health for this command? Check out Copy-DbaLogin.

Synopsis

Migrates logins from source to destination SQL Servers. Supports SQL Server versions 2000 and newer.

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.

Syntax

Copy-DbaLogin
    [-SourceSqlCredential <PSCredential>]
    [-DestinationSqlCredential <PSCredential>]
    [-Login <Object[]>]
    [-ExcludeLogin <Object[]>]
    [-ExcludeSystemLogins]
    [-LoginRenameHashtable <Hashtable>]
    [-KillActiveConnection]
    [-NewSid]
    [-Force]
    [-ObjectLevel]
    [-ExcludePermissionSync]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Copy-DbaLogin -Source <DbaInstanceParameter>
    [-SourceSqlCredential <PSCredential>]
    -Destination <DbaInstanceParameter[]>
    [-DestinationSqlCredential <PSCredential>]
    [-Login <Object[]>]
    [-ExcludeLogin <Object[]>]
    [-ExcludeSystemLogins]
    [-SyncSaName]
    [-LoginRenameHashtable <Hashtable>]
    [-KillActiveConnection]
    [-NewSid]
    [-Force]
    [-ObjectLevel]
    [-ExcludePermissionSync]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Copy-DbaLogin -Source <DbaInstanceParameter>
    [-SourceSqlCredential <PSCredential>]
    [-DestinationSqlCredential <PSCredential>]
    [-Login <Object[]>]
    [-ExcludeLogin <Object[]>]
    [-ExcludeSystemLogins]
    -OutFile <String>
    [-LoginRenameHashtable <Hashtable>]
    [-KillActiveConnection]
    [-NewSid]
    [-Force]
    [-ObjectLevel]
    [-ExcludePermissionSync]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Copy-DbaLogin
    [-SourceSqlCredential <PSCredential>]
    -Destination <DbaInstanceParameter[]>
    [-DestinationSqlCredential <PSCredential>]
    [-Login <Object[]>]
    [-ExcludeLogin <Object[]>]
    [-ExcludeSystemLogins]
    [-InputObject <Object[]>]
    [-LoginRenameHashtable <Hashtable>]
    [-KillActiveConnection]
    [-NewSid]
    [-Force]
    [-ObjectLevel]
    [-ExcludePermissionSync]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Copy-DbaLogin
    [-SourceSqlCredential <PSCredential>]
    [-DestinationSqlCredential <PSCredential>]
    [-Login <Object[]>]
    [-ExcludeLogin <Object[]>]
    [-ExcludeSystemLogins]
    [-SyncSaName]
    [-LoginRenameHashtable <Hashtable>]
    [-KillActiveConnection]
    [-NewSid]
    [-Force]
    [-ObjectLevel]
    [-ExcludePermissionSync]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

 

Examples

 

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 as newlogin.

Example: 6
PS C:\> Copy-DbaLogin -LoginRenameHashtable @{ OldLogin = "NewLogin" } -Source Sql01 -Destination Sql01 -Login ORG\OldLogin -ObjectLevel -NewSid

Clones OldLogin as NewLogin onto the same server, generating a new SID for the login. Also clones object-level permissions.

Example: 7
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: 8
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.

Required Parameters

-Source

Source SQL Server. You must have sysadmin access and server version must be SQL Server version 2000 or higher.

Alias
Required True
Pipeline false
Default Value
-Destination

Destination SQL Server. You must have sysadmin access and the server must be SQL Server 2000 or higher.

Alias
Required True
Pipeline false
Default Value
-OutFile

Calls Export-DbaLogin and exports all logins to a T-SQL formatted file. This does not perform a copy, so no destination is required.

Alias
Required True
Pipeline false
Default Value

Optional Parameters

-SourceSqlCredential

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.
Alias
Required False
Pipeline false
Default Value
-DestinationSqlCredential

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.
Alias
Required False
Pipeline false
Default Value
-Login

The login(s) to process. Options for this list are auto-populated from the server. If unspecified, all logins will be processed.
Alias
Required False
Pipeline false
Default Value
-ExcludeLogin

The login(s) to exclude. Options for this list are auto-populated from the server.
Alias
Required False
Pipeline false
Default Value
-ExcludeSystemLogins

If this switch is enabled, NT SERVICE accounts will be skipped.
Alias
Required False
Pipeline false
Default Value False
-SyncSaName

If this switch is enabled, the name of the sa account will be synced between Source and Destination
Alias
Required False
Pipeline false
Default Value False
-InputObject

Takes the parameters required from a Login object that has been piped into the command
Alias
Required False
Pipeline true (ByValue)
Default Value
-LoginRenameHashtable

Pass a hash table into this parameter to create logins under different names based on hashtable mapping.
Alias
Required False
Pipeline false
Default Value
-KillActiveConnection

A login cannot be dropped when it has active connections on the instance. If this switch is enabled, all active connections and sessions on Destination will be killed.
Alias
Required False
Pipeline false
Default Value False
-NewSid

Ignore sids from the source login objects to generate new sids on the destination server. Useful when copying login onto the same server
Alias
Required False
Pipeline false
Default Value False
-Force

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.
Alias
Required False
Pipeline false
Default Value False
-ObjectLevel

Include object-level permissions for each user associated with copied login.
Alias
Required False
Pipeline false
Default Value False
-ExcludePermissionSync

Skips permission syncs
Alias
Required False
Pipeline false
Default Value False
-EnableException

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.
Alias
Required False
Pipeline false
Default Value False
-WhatIf

If this switch is enabled, no actions are performed but informational messages will be displayed that explain what would happen if the command were to run.
Alias wi
Required False
Pipeline false
Default Value
-Confirm

If this switch is enabled, you will be prompted for confirmation before executing any operations that change state.
Alias cf
Required False
Pipeline false
Default Value