Test-DbaAgentJobOwner

Want to see the source code for this command? Check out Test-DbaAgentJobOwner on GitHub.
Want to see the Bill Of Health for this command? Check out Test-DbaAgentJobOwner.

Synopsis

Identifies SQL Agent jobs with incorrect ownership for security compliance auditing

Description

This function audits SQL Agent job ownership by comparing each job's current owner against a target login, typically 'sa' or another sysadmin account. Jobs owned by inappropriate accounts can pose security risks, especially if those accounts are disabled, deleted, or have reduced permissions. By default, it checks against the 'sa' account (or renamed sysadmin), but you can specify any valid login for your organization's security standards. Returns only jobs that don't match the expected ownership, making it easy to identify compliance violations that need remediation.

Best practice reference: https://www.itprotoday.com/sql-server-tip-assign-ownership-jobs-sysadmin-account

Syntax

Test-DbaAgentJobOwner
    [-SqlInstance] <DbaInstanceParameter[]>
    [[-SqlCredential] <PSCredential>]
    [[-Job] <Object[]>]
    [[-ExcludeJob] <Object[]>]
    [[-Login] <String>]
    [-EnableException]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Test-DbaAgentJobOwner -SqlInstance localhost

Returns all SQL Agent Jobs where the owner does not match 'sa'.

Example: 2

PS C:\> Test-DbaAgentJobOwner -SqlInstance localhost -ExcludeJob 'syspolicy_purge_history'

Returns SQL Agent Jobs except for the syspolicy_purge_history job

Example: 3

PS C:\> Test-DbaAgentJobOwner -SqlInstance localhost -Login DOMAIN\account

Returns all SQL Agent Jobs where the owner does not match DOMAIN\account. Note
that Login must be a valid security principal that exists on the target server.

Required Parameters

-SqlInstance

The target SQL Server instance or instances.

Optional Parameters

-SqlCredential

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.

-Job

Specifies specific SQL Agent jobs to check for ownership compliance. When provided, only these named jobs are evaluated against the target owner.
Use this to focus on critical jobs or when troubleshooting specific ownership issues. If omitted, all jobs on the instance are processed.

-ExcludeJob

Excludes specific SQL Agent jobs from the ownership compliance check. Useful for skipping system jobs or jobs that legitimately require different owners.
Commonly used to exclude jobs like 'syspolicy_purge_history' or maintenance jobs that run under service accounts by design.

-Login

Specifies the target login that should own SQL Agent jobs for security compliance. Must be an existing login on the server, cannot be a Windows Group.
Defaults to 'sa' (or the renamed sysadmin account). Common alternatives include service accounts or dedicated job owner logins required by your organization's security policies.

-EnableException

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.