dbatools is a free PowerShell module with over 200 SQL Server administration, best practice and migration commands included.

Please note that documentation and command names may be out of date while we work furiously towards 1.0


Set-DbaJobOwner

This command will alter SQL Agent Job ownership to match a specified login if their current owner does not match the target login. By default, the target login will be ‘sa’ (or the renamed sa account). This command also supports auto-populated -Jobs and -Exclude params to explicitly include or exclude jobs.

Screenshots

Set-DbaJobOwner

Examples

To set the SQL Agent Job owner to sa on all jobs where the owner does not match sa.

Set-DbaJobOwner -SqlServer localhost

To set the SQL Agent Job owner to sa on all jobs where the owner does not match ‘DOMAIN\account’. Note that TargetLogin must be a valid security principal that exists on the target server.

Set-DbaJobOwner -SqlServer localhost -TargetLogin DOMAIN\account

To set the SQL Agent Job owner to ‘sa’ on the job1 and job2 jobs if their current owner does not match ‘sa’.

Set-DbaJobOwner -SqlServer localhost -Job job1, job2

To set the SQL Agent Job owner to sa on all jobs where the owner does not match sa on both sqlserver and sql2016.

‘sqlserver’,’sql2016′ | Set-DbaJobOwner

References

SQL Server Magazine: Assign Ownership of Jobs to the SysAdmin Account.

Author

This command was created by Mike Fal. You can find Mike on Twitter, mikefal.net and LinkedIn.

Get-Help

From PowerShell, execute Get-Help Set-DbaJobOwner -Detailed for more information on this function.

Source Code

Want to see the source code? View Set-DbaJobOwner.ps1 on GitHub
 

Related commands