Orphan login report

# Import dbatools module (Install if not already installed)

Import-Module dbatools -ErrorAction Stop

# Define SQL Server Instance

$SQLInstance = "YourSQLServerName"  # Change this to your SQL Server instance

# Report File Path

$ReportFile = "C:\Inactive_SQL_AD_Users.csv"

# Fetch all Windows logins directly from SQL Server

$SQLLogins = Get-DbaLogin -SqlInstance $SQLInstance | Where-Object { $_.LoginType -match 'Windows' }

# Array to store inactive/missing users

$InactiveUsers = @()

# Check each login in Active Directory using Test-DbaWindowsLogin

foreach ($Login in $SQLLogins) {

    $UserName = $Login.Name.Trim()

    # Test if the login exists in AD

    $TestResult = Test-DbaWindowsLogin -SqlInstance $SQLInstance -Login $UserName

    if (!$TestResult.Success) {

        $InactiveUsers += [PSCustomObject]@{

            LoginName = $UserName

            Status = "Not Found in AD"

        }

    }

}

# Generate the report if there are missing users

if ($InactiveUsers.Count -gt 0) {

    $InactiveUsers | Export-Csv $ReportFile -NoTypeInformation

    Write-Host "Report generated at $ReportFile"

} else {

    Write-Host "No inactive users found in SQL Server."

}