Author | Stephen Bennett, sqlnotesfromtheunderground.wordpress.com , Chrissy LeMaire (@cl) |
Availability | Windows, Linux, macOS |
Want to see the source code for this command? Check out Test-DbaWindowsLogin on GitHub.
Want to see the Bill Of Health for this command? Check out Test-DbaWindowsLogin.
Validates Windows logins and groups in SQL Server against Active Directory to identify orphaned, disabled, or problematic accounts
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.
Test-DbaWindowsLogin
[[-SqlInstance] <DbaInstanceParameter[]>]
[[-SqlCredential] <PSCredential>]
[[-Login] <String[]>]
[[-ExcludeLogin] <String[]>]
[[-FilterBy] <String>]
[[-IgnoreDomains] <String[]>]
[[-InputObject] <Login[]>]
[-EnableException]
[<CommonParameters>]
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
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
PS C:\> Test-DbaWindowsLogin -SqlInstance Dev01 -IgnoreDomains testdomain
Tests all Domain logins excluding any that are from the testdomain
PS C:\> Get-DbaLogin -SqlInstance Dev01 -Login DOMAIN\User | Test-DbaWindowsLogin
Tests only the login returned by Get-DbaLogin
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.
Alias | |
Required | False |
Pipeline | true (ByValue) |
Default Value |
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.
Alias | |
Required | False |
Pipeline | false |
Default Value |
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.
Alias | |
Required | False |
Pipeline | false |
Default Value |
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.
Alias | |
Required | False |
Pipeline | false |
Default Value |
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.
Alias | |
Required | False |
Pipeline | false |
Default Value | None |
Accepted Values | LoginsOnly,GroupsOnly,None |
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.
Alias | |
Required | False |
Pipeline | false |
Default Value |
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.
Alias | |
Required | False |
Pipeline | true (ByValue) |
Default Value |
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.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |