dataplat
Copy-DbaLogin cannot clone a login on AG primary
Verified issue does not already exist?
Yes
What error did you receive?
Attempting to use Copy-DbaLogin to clone an existing user (Example 6 of the Copy-DbaLogin docs) fails to clone database permissions for databases in an Availability Group, even if the server is the primary replica.
Verbose output shows messages that AG databases are being skipped:
VERBOSE: [10:28:05][Connect-DbaInstance] Server object passed in, will do some checks and then return the original object
VERBOSE: [10:28:06][Update-SqlPermission] Database [AGDATABASE] is part of an availability group. Skipping.
Expected behavior would be if the server is the primary replica, database permissions should be created.
Steps to Reproduce
Copy-DbaLogin -Source SERVERA -Destination SERVERA -Login 'DOMAIN\OldUser' -ObjectLevel -LoginRenameHashtable @{'DOMAIN\OldUser' = 'DOMAIN\NewUser'}
Are you running the latest release?
Yes
Other details or mentions
No response
What PowerShell host was used when producing this error
Windows PowerShell (powershell.exe)
PowerShell Host Version
Name Value
PSVersion 5.1.19041.1682
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.19041.1682
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1
SQL Server Edition and Build number
Microsoft SQL Server 2016 (SP2-CU17) (KB5001092) - 13.0.5888.11 (X64) Mar 19 2021 19:41:38 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)
.NET Framework Version
PSChildName Version
v2.0.50727 2.0.50727.4927 v3.0 3.0.30729.4926 Windows Communication Foundation 3.0.4506.4926 Windows Presentation Foundation 3.0.6920.4902 v3.5 3.5.30729.4926 Client 4.8.04084 Full 4.8.04084 Client 4.0.0.0
I agree that the test is to strict. Maybe they wanted to test for a database on a secondary replica? @potatoqualitee - do you remember the reason for the test?
Perhaps the assumption is that the permissions were already set? I'm not sure. @wsmelton do you recall?
Yeah primary intent was to copy logins from a primary to secondary replicas to keep them in sync. Can't say I've heard of it being used to clone logins on the same server. I think the pattern we expected users to use was via Get-DbaLogin ... | New-DbaLogin ... instead of the copy command.
The blocker with the copy command would be in Update-SqlPermission:
https://github.com/dataplat/dbatools/blob/131c10eb34275c6cf8ebe46809c84030df233b88/internal/functions/Update-SqlPermission.ps1#L211-L214
If we want to open that up as an alternative if users are reaching for that command first, don't see any reason why not. We just add a check in the block above to validate the destination is primary, or the database is writable.
It would be good to have this capability as I'm currently restricted from copying specific logins from an non-AG server into a new AG server. Thanks.
I've have confirmed that the issue has now been resolved. Thank you.