copy-db with copy users and logins

[Skyscraper76]

Summarize Command's Functionality

For automation it will be very nice, if you can implement a parameter in copy-dbadatabse, that will check the login on the source and check on the destination if these objects already exists.
Further if there are orphaned user these can be repaired.

Is there a command that is similiar or close to what you are looking for?

Yes

Technical Details

copy-dbadatabse

[potatoqualitee]

i like that, actually. what do you think @ClaudioESSilva ?

[ClaudioESSilva]

Hi @Skyscraper76, do you mind elaborating a bit more?

You copy a database and that new parameter would check if the logins associated with the users existing on that database are part of the logins on the destination instance? 🤔

If not should copy those logins over?
So it's like a "check dependencies" (login-wise) for the users that belong to the database?

[Skyscraper76]

hi, you are right. 12.10.2022 17:53:34 Cláudio Silva ***@***.***>:

…

Hi @Skyscraper76[https://github.com/Skyscraper76], do you mind elaborating a bit more? You copy a database and that new parameter would check if the logins associated with the users existing on that database are part of the logins on the destination instance? 🤔 If not should copy those logins over? So it's like a "check dependencies" (login-wise) for the users that belong to the database? — Reply to this email directly, view it on GitHub[#8587 (comment)], or unsubscribe[https://github.com/notifications/unsubscribe-auth/ATSOZLRBZWW4EXGNJ4LH7A3WC3NH3ANCNFSM6AAAAAARDLZ2SY]. You are receiving this because you were mentioned.[Verfolgungsbild][https://github.com/notifications/beacon/ATSOZLQTDF7N43LNXP63QVDWC3NH3A5CNFSM6AAAAAARDLZ2S2WGG33NNVSW45C7OR4XAZNMJFZXG5LFINXW23LFNZ2KUY3PNVWWK3TUL5UWJTSMCRJNG.gif]

[andreasjordan]

@claude please give me some advice on this.

[claude]

Claude finished @andreasjordan's task in 1m 16s —— View job

---

Advice on Implementing Login Copy with Copy-DbaDatabase

• Gather context
• Read Copy-DbaDatabase implementation
• Read Copy-DbaLogin and Repair-DbaDbOrphanUser
• Formulate advice

---

The feature request is well-suited for a new -CopyLogins switch parameter on Copy-DbaDatabase. The building blocks already exist in dbatools — you'd be orchestrating Copy-DbaLogin and Repair-DbaDbOrphanUser together.

How it Would Work

After each successful database copy, the new switch would:

1. Identify which logins are needed — scan the source database's users (via Get-DbaDbUser), filter out system users (dbo, guest, INFORMATION_SCHEMA, sys), and collect their associated login names
2. Copy only the missing logins — call Copy-DbaLogin with those specific login names (it already handles "login already exists" gracefully)
3. Repair orphaned users — call Repair-DbaDbOrphanUser on the destination for the copied database

Implementation Sketch

# Inside the process block, after a successful database copy:
if ($CopyLogins) {
    $sourceLogins = Get-DbaDbUser -SqlInstance $sourceServer -Database $dbName -ExcludeSystemUser |
        Where-Object { $_.Login -ne "" } |
        Select-Object -ExpandProperty Login -Unique

    if ($sourceLogins) {
        $splatCopyLogin = @{
            Source                = $Source
            SourceSqlCredential   = $SourceSqlCredential
            Destination           = $destServer
            DestinationSqlCredential = $DestinationSqlCredential
            Login                 = $sourceLogins
            EnableException       = $false
        }
        Copy-DbaLogin @splatCopyLogin
    }

    $splatRepair = @{
        SqlInstance     = $destServer
        Database        = $destDbName
        EnableException = $false
    }
    Repair-DbaDbOrphanUser @splatRepair
}

Key Considerations for Implementation

Parameter naming: -CopyLogins is consistent with dbatools conventions (verb-like switch describing what action is taken).

Where to insert the logic: The copy logic happens in two internal functions (Copy-SqlDatabase within the begin block). After a successful restore/attach (where $copyResult.Status -eq "Successful"), the login copy + repair should run.

Multiple destinations: Copy-DbaDatabase supports multiple -Destination targets. The login copy should run once per destination (not once per source), and Repair-DbaDbOrphanUser should target each destination separately.

Credential passthrough: The SourceSqlCredential and DestinationSqlCredential already present on Copy-DbaDatabase should be forwarded to Copy-DbaLogin and Repair-DbaDbOrphanUser.

Renamed databases: When -NewName or -Prefix is used, Repair-DbaDbOrphanUser must use the destination database name (not the source name).

Workaround Until Implemented

Users can chain the commands manually today:

$result = Copy-DbaDatabase -Source sql1 -Destination sql2 -Database TestDB -BackupRestore -SharedPath \\share\backups

# Get logins used by the source database
$logins = Get-DbaDbUser -SqlInstance sql1 -Database TestDB -ExcludeSystemUser |
    Where-Object Login -ne "" |
    Select-Object -ExpandProperty Login -Unique

# Copy those logins
Copy-DbaLogin -Source sql1 -Destination sql2 -Login $logins

# Fix any orphaned users
Repair-DbaDbOrphanUser -SqlInstance sql2 -Database TestDB

This is a clean, self-contained feature with well-defined scope. The main complexity is handling the multiple-destination case and ensuring the destination database name is correct when -NewName/-Prefix are used.