--- title: "Test-DbaWindowsLogin" description: "Validates Windows logins and groups in SQL Server against Active Directory to identify orphaned, disabled, or problematic accounts" url: "https://dbatools.io/Test-DbaWindowsLogin/" availability: "Windows only" tags: ["Login"] author: "Stephen Bennett, sqlnotesfromtheunderground.wordpress.com | Chrissy LeMaire (@cl)" source: "https://github.com/dataplat/dbatools/blob/master/public/Test-DbaWindowsLogin.ps1" last_updated: "2024-01-01" --- # 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 ```powershell Test-DbaWindowsLogin [[-SqlInstance] ] [[-SqlCredential] ] [[-Login] ] [[-ExcludeLogin] ] [[-FilterBy] ] [[-IgnoreDomains] ] [[-InputObject] ] [-EnableException] [] ``` ## Examples ### Example 1: Tests all logins in the current Active Directory domain that are either disabled or do not exist on the SQL... ```powershell 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: Tests all Active Directory groups that have logins on Dev01, and shows all information for those logins ```powershell PS C:\> Test-DbaWindowsLogin -SqlInstance Dev01 -FilterBy GroupsOnly | Select-Object -Property * ``` ### Example 3: Tests all Domain logins excluding any that are from the testdomain ```powershell PS C:\> Test-DbaWindowsLogin -SqlInstance Dev01 -IgnoreDomains testdomain ``` ### Example 4: Tests only the login returned by Get-DbaLogin ```powershell PS C:\> Get-DbaLogin -SqlInstance Dev01 -Login DOMAIN\User | Test-DbaWindowsLogin ``` ### 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. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | true (ByValue) | | Default Value | | ##### -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. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -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. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -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. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -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. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | None | | Accepted Values | LoginsOnly,GroupsOnly,None | ##### -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. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -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. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | true (ByValue) | | Default Value | | ##### -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. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | False | ## 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 *. --- Part of [dbatools](https://dbatools.io/), a free and open source PowerShell module for SQL Server administration. Full command index: https://dbatools.io/commands/