Sql-server – ProcessID Owner with third party app

sql serversql-server-2008-r2windows

I am having problems trying to audit a third party app with a SQLServer backend.

The app manages users and user access via a SQL table, using a single SQL login to access the databases.

I am trying to audit this by using the Host PID much like Windows Task Manager associates a PID and Owner for every process.

Here is what I have attempted and tried

  • I was able to figure out how to extract the app.exe PID.
  • I cannot figure out how to get the Windows Owner associated to that PID.
  • I have tried using xp_cmdshell to query the Windows tasklist and even wrote a .Net console app which gets called by SQL to collect the information but every time I try to extract the Owner it is blank.

Any thoughts on how I can get the Owner?

Best Answer

I cannot figure out how to get the Windows Owner associated to that PID.

You can get the owner using PowerShell:

# Identify the name of the remote computer you want to query
$computerName = 'SomeRemoteComputerName'
# Invoke the command on the remote system to get the information that is necessary
Invoke-Command -ComputerName $computerName -ScriptBlock {
    # Build a hashtable that associates process ids with owners
    $processOwners = @{}
    Get-WmiObject -Class Win32_Process | ForEach-Object {
        $processOwner = $_.GetOwner()
        # Combine the domain and user information together to get the process owner
        $processOwners[[int]$_.ProcessId] = $processOwner.Domain + '\' + $processOwner.User 
    }
    # Now get all processes and add the owner information to them
    Get-Process | ForEach-Object {
        $processOwner = $null
        # If we have process owner information for the process, look up the owner in the table
        if ($processOwners.ContainsKey($_.Id)) {
            $processOwner = $processOwners[$_.Id]
        }
        # Add the owner information to the current process object
        Add-Member -InputObject $_ -MemberType NoteProperty -Name Owner -Value $processOwner
        # Return the current process object from the script block
        $_
    } | Select-Object Name,Owner,Description
}

script source

Related Question