Test-DbaWindowsLogin

Synopsis

Validates Windows logins and groups in SQL Server against Active Directory to identify orphaned, disabled, or problematic accounts

Description

Queries SQL Server for all Windows-based logins and groups, then validates each against Active Directory to identify security issues and cleanup opportunities. The function checks whether AD accounts still exist, are enabled, and match their SQL Server SID to detect orphaned logins from domain migrations or account deletions. This helps DBAs maintain login security by identifying stale Windows authentication accounts that should be removed from SQL Server.

Syntax

Test-DbaWindowsLogin
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-Login] <String[]>]
    [[-ExcludeLogin] <String[]>]
    [[-FilterBy] <String>]
    [[-IgnoreDomains] <String[]>]
    [[-InputObject] <Login[]>]
    [-EnableException]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Test-DbaWindowsLogin -SqlInstance Dev01

Tests all logins in the current Active Directory domain that are either disabled or do not exist on the SQL Server instance Dev01

Example: 2

PS C:\> Test-DbaWindowsLogin -SqlInstance Dev01 -FilterBy GroupsOnly | Select-Object -Property *

Tests all Active Directory groups that have logins on Dev01, and shows all information for those logins

Example: 3

PS C:\> Test-DbaWindowsLogin -SqlInstance Dev01 -IgnoreDomains testdomain

Tests all Domain logins excluding any that are from the testdomain

Example: 4

PS C:\> Get-DbaLogin -SqlInstance Dev01 -Login DOMAIN\User | Test-DbaWindowsLogin

Tests only the login returned by Get-DbaLogin

Optional Parameters

-SqlInstance

The SQL Server instance you’re checking logins on. You must have sysadmin access and server version must be SQL Server version 2000 or higher.

-SqlCredential

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.

-Login

Specifies specific Windows logins to validate against Active Directory. Use this when you want to test only certain logins rather than all Windows accounts on the server.
Accepts wildcards and multiple values. Helpful for focused security audits of high-privilege accounts or problem logins.

-ExcludeLogin

Excludes specific Windows logins from validation checks. Use this to skip service accounts or known system logins that you don’t need to audit.
Accepts wildcards and multiple values. Common exclusions include application service accounts and break-glass emergency accounts.

-FilterBy

Limits validation to either individual user accounts or Active Directory groups. Use ‘LoginsOnly’ when auditing user access or ‘GroupsOnly’ when reviewing group-based permissions.
Default of ‘None’ validates both types. GroupsOnly is useful for reviewing role-based access control implementation.

-IgnoreDomains

Excludes logins from specific Active Directory domains from validation. Use this in multi-domain environments to focus on specific domains or skip legacy/untrusted domains.
Helpful when you have old domain trusts or want to audit only production domains while excluding development or test domains.

-InputObject

Accepts login objects from Get-DbaLogin for targeted validation. Use this when you want to validate a specific subset of logins already retrieved by another command.
Enables powerful filtering scenarios by piping pre-filtered login objects instead of processing all Windows logins on the server.

-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 per Windows login or Active Directory group validated. Each object represents the validation results for a single Windows login or group on the SQL Server instance.

Windows User and Computer Account Properties:

• Server: Full SQL Server instance name (computer\instance)
• Domain: Active Directory domain name
• Login: Windows login username
• Type: Account type - either “User” or “Computer”
• Found: Boolean indicating whether the account exists in Active Directory
• SamAccountNameMismatch: Boolean indicating whether the SAM account name differs between SQL Server and AD
• DisabledInSQLServer: Boolean indicating whether the login is disabled in SQL Server
• Enabled: Boolean indicating whether the account is enabled in Active Directory (null for groups)
• AccountNotDelegated: Boolean indicating whether the account is marked as non-delegated
• LockedOut: Boolean indicating whether the account is currently locked out
• PasswordExpired: Boolean indicating whether the password has expired
• PasswordNeverExpires: Boolean indicating whether the password is set to never expire
• PasswordNotRequired: Boolean indicating whether a password is required for the account
• CannotChangePassword: Boolean indicating whether the user account is password-change protected
• AllowReversiblePasswordEncryption: Boolean indicating whether reversible password encryption is allowed
• SmartcardLogonRequired: Boolean indicating whether smartcard logon is required
• TrustedForDelegation: Boolean indicating whether the account is trusted for delegation
• UserAccountControl: Raw integer UserAccountControl value from Active Directory

Windows Group Account Properties:

• Server, Domain, Login, Type, Found, SamAccountNameMismatch, DisabledInSQLServer: Same as above
• All security properties (Enabled, AccountNotDelegated, LockedOut, etc.): null for group accounts

Default display properties (via Select-DefaultView):

• Server, Domain, Login, Type, Found, SamAccountNameMismatch, DisabledInSQLServer All properties are accessible using Select-Object *.