Compare-DbaLogin

Synopsis

Compares SQL Server logins between a source and one or more destination instances.

Description

Compares SQL Server logins between a source instance and one or more destination instances to identify which logins exist only on the source, only on the destination, or on both. This is useful for identifying logins that would be lost when using Copy-DbaLogin with -Force, or for auditing login consistency between environments.

Returns one object per login per destination instance, indicating whether the login exists on the source, destination, or both.

Syntax

Compare-DbaLogin
    [-Source] <DbaInstanceParameter>
    [[-SourceSqlCredential] <PSCredential>]
    [-Destination] <DbaInstanceParameter[]>
    [[-DestinationSqlCredential] <PSCredential>]
    [[-Login] <String[]>]
    [[-ExcludeLogin] <String[]>]
    [-ExcludeSystemLogin]
    [-EnableException]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Compare-DbaLogin -Source sql1 -Destination sql2

Compares all logins between sql1 and sql2, returning the status of each login.

Example: 2

PS C:\> Compare-DbaLogin -Source sql1 -Destination sql2 | Where-Object Status -eq "DestinationOnly"

Returns logins that exist on sql2 but not on sql1. These logins would be lost if Copy-DbaLogin -Force were run from sql1 to sql2.

Example: 3

PS C:\> Compare-DbaLogin -Source sql1 -Destination sql2 | Where-Object Status -eq "SourceOnly"

Returns logins that exist on sql1 but not on sql2. These are the logins that Copy-DbaLogin would create.

Example: 4

PS C:\> Compare-DbaLogin -Source sql1 -Destination sql2 -ExcludeSystemLogin

Compares user-created logins between sql1 and sql2, excluding built-in system logins.

Example: 5

PS C:\> Compare-DbaLogin -Source sql1 -Destination sql2, sql3 -Login "appuser", "reportuser"

Compares the specified logins between sql1 and both sql2 and sql3.

Required Parameters

-Source

The source SQL Server instance.

-Destination

The destination SQL Server instance or instances.

Optional Parameters

-SourceSqlCredential

Login to the source 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.

-DestinationSqlCredential

Login to the destination 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.

-Login

Specifies one or more logins to include in the comparison. All other logins are excluded.

-ExcludeLogin

Specifies one or more logins to exclude from the comparison.

-ExcludeSystemLogin

Excludes built-in system logins from the comparison results.

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

Outputs

PSCustomObject

Returns one object for each login found on either the source or destination instance.

Properties:

• SourceServer: The name of the source SQL Server instance
• DestinationServer: The name of the destination SQL Server instance
• LoginName: The name of the login account
• LoginType: The login type (SqlLogin, WindowsUser, WindowsGroup, etc.)
• Status: Indicates where the login exists - “SourceOnly”, “DestinationOnly”, or “Both”