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

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

Write-Host "=== DBAOps Permission 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 server-level permissions
        $permQuery = @"
SELECT 
    pr.name AS PrincipalName,
    pr.type_desc AS PrincipalType,
    pe.permission_name AS Permission,
    pe.state_desc AS PermissionState,
    'SERVER' AS Scope
FROM sys.server_permissions pe
JOIN sys.server_principals pr ON pe.grantee_principal_id = pr.principal_id
WHERE pr.type IN ('S', 'U', 'G')
  AND pr.name NOT LIKE 'NT %'
  AND pr.name NOT LIKE '##%'
UNION ALL
SELECT 
    pr.name AS PrincipalName,
    pr.type_desc AS PrincipalType,
    r.name AS Permission,
    'MEMBER' AS PermissionState,
    'SERVER ROLE' AS Scope
FROM sys.server_role_members rm
JOIN sys.server_principals pr ON rm.member_principal_id = pr.principal_id
JOIN sys.server_principals r ON rm.role_principal_id = r.principal_id
WHERE pr.type IN ('S', 'U', 'G')
  AND pr.name NOT LIKE 'NT %'
  AND pr.name NOT LIKE '##%'
"@
        
        $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 = $permQuery
        $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) {
                $principalName = ($r.PrincipalName -replace "'", "''")
                
                # Check if this is a new permission (drift)
                $checkSQL = @"
IF NOT EXISTS (
    SELECT 1 FROM audit.PermissionBaseline 
    WHERE ServerName = '$ServerName' 
      AND PrincipalName = '$principalName' 
      AND Permission = '$($r.Permission)'
      AND Scope = '$($r.Scope)'
)
BEGIN
    INSERT INTO audit.PermissionDrift (ServerName, PrincipalName, PrincipalType, Permission, PermissionState, Scope, DriftType, DetectedAt)
    VALUES ('$ServerName', '$principalName', '$($r.PrincipalType)', '$($r.Permission)', '$($r.PermissionState)', '$($r.Scope)', 'NEW', GETDATE())
    
    INSERT INTO audit.PermissionBaseline (ServerName, PrincipalName, PrincipalType, Permission, PermissionState, Scope, CollectedAt)
    VALUES ('$ServerName', '$principalName', '$($r.PrincipalType)', '$($r.Permission)', '$($r.PermissionState)', '$($r.Scope)', 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) permissions" -ForegroundColor Green
        } else {
            Write-Host " - 0 permissions" -ForegroundColor Gray
        }
    }
    catch {
        Write-Host " - ERROR" -ForegroundColor Yellow
    }
}

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