# Invoke-LoginDriftCheck.ps1
# Uses CMS as source of truth
# Version 1.0

param(
    [string]$SqlInstance = "YOURSERVER",
    [string]$Database = "JBRhoylleRepository"
)

Write-Host "=== DBAOps Login Drift Monitor ===" -ForegroundColor Cyan
Write-Host "Started: $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')"

# Get servers from CMS - SOURCE OF TRUTH
$cmsQuery = @"
SELECT server_name AS ServerName
FROM msdb.dbo.sysmanagement_shared_registered_servers_internal
WHERE server_name NOT LIKE '%\%'
  AND server_name <> @@SERVERNAME
ORDER BY server_name
"@

try {
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = "Server=$SqlInstance;Database=msdb;Integrated Security=True;TrustServerCertificate=True"
    $connection.Open()
    
    $command = $connection.CreateCommand()
    $command.CommandText = $cmsQuery
    $adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataset) | Out-Null
    $connection.Close()
    
    $Servers = $dataset.Tables[0]
    Write-Host "Found $($Servers.Rows.Count) servers from CMS" -ForegroundColor Green
}
catch {
    Write-Host "ERROR getting servers from CMS: $_" -ForegroundColor Red
    exit 1
}

$collected = 0
$driftFound = 0

foreach ($row in $Servers.Rows) {
    $ServerName = $row.ServerName
    Write-Host "  Checking: $ServerName" -ForegroundColor Gray -NoNewline
    
    try {
        # Query for SQL logins
        $loginQuery = @"
SELECT 
    name AS LoginName,
    type_desc AS LoginType,
    is_disabled AS IsDisabled,
    create_date AS CreateDate,
    modify_date AS ModifyDate,
    default_database_name AS DefaultDatabase
FROM sys.server_principals
WHERE type IN ('S', 'U', 'G')
  AND name NOT LIKE 'NT %'
  AND name NOT LIKE '##%'
  AND name NOT IN ('sa', 'public')
ORDER BY name
"@
        
        $conn = New-Object System.Data.SqlClient.SqlConnection
        $conn.ConnectionString = "Server=$ServerName;Database=master;Integrated Security=True;TrustServerCertificate=True;Connect Timeout=10"
        $conn.Open()
        
        $cmd = $conn.CreateCommand()
        $cmd.CommandText = $loginQuery
        $cmd.CommandTimeout = 30
        
        $adapt = New-Object System.Data.SqlClient.SqlDataAdapter $cmd
        $ds = New-Object System.Data.DataSet
        $adapt.Fill($ds) | Out-Null
        $conn.Close()
        
        $results = $ds.Tables[0]
        
        if ($results.Rows.Count -gt 0) {
            $repoConn = New-Object System.Data.SqlClient.SqlConnection
            $repoConn.ConnectionString = "Server=$SqlInstance;Database=$Database;Integrated Security=True;TrustServerCertificate=True"
            $repoConn.Open()
            
            foreach ($r in $results.Rows) {
                $loginName = ($r.LoginName -replace "'", "''")
                
                # Check if this is a new login (drift)
                $checkSQL = @"
IF NOT EXISTS (
    SELECT 1 FROM audit.LoginBaseline 
    WHERE ServerName = '$ServerName' 
      AND LoginName = '$loginName'
)
BEGIN
    INSERT INTO audit.LoginDrift (ServerName, LoginName, LoginType, IsDisabled, DriftType, DetectedAt)
    VALUES ('$ServerName', '$loginName', '$($r.LoginType)', $([int]$r.IsDisabled), 'NEW', GETDATE())
    
    INSERT INTO audit.LoginBaseline (ServerName, LoginName, LoginType, IsDisabled, DefaultDatabase, CollectedAt)
    VALUES ('$ServerName', '$loginName', '$($r.LoginType)', $([int]$r.IsDisabled), '$($r.DefaultDatabase)', GETDATE())
END
"@
                
                try {
                    $insertCmd = $repoConn.CreateCommand()
                    $insertCmd.CommandText = $checkSQL
                    $rowsAffected = $insertCmd.ExecuteNonQuery()
                    if ($rowsAffected -gt 0) { $driftFound++ }
                    $collected++
                } catch { }
            }
            $repoConn.Close()
            Write-Host " - $($results.Rows.Count) logins" -ForegroundColor Green
        } else {
            Write-Host " - 0 logins" -ForegroundColor Gray
        }
    }
    catch {
        Write-Host " - ERROR" -ForegroundColor Yellow
    }
}

Write-Host ""
Write-Host "=== Login Drift Check Complete ===" -ForegroundColor Cyan
Write-Host "  Logins checked: $collected"
Write-Host "  New drift detected: $driftFound"
