Problem
We’ve run into issues where SQL Server Agent Job or database owners have left the company, or an account was deprecated or changed. We want to automate updating these owners since some of the SQL Server Agent Jobs have failed, creating outages, or we’ve had issues with the database because of the ownership. Since our environment has many servers, is there a way to simplify and automate this with PowerShell?
Solution
The following includes two PowerShell scripts that can be used to update the owner information. The first is for SQL Server Agent Job ownership and the second is for SQL Server database ownership.
In the below scripts, we’ll write out the result of a change to the console and we can save this to a file with the server as a suggestion because when changing object owners, I’d highly suggest retaining proof of the change, like a bank deposit receipt.
In both scenarios, I look for the owner where the new change parameter is the official owner that everything should be, but we know that in some cases – like creating replication – the job will take on the owner of the creator and we can go in and change it after the creation. In addition, if we have a set up where the owners are always supposed to be a user like DOM\sa, then we can use these scripts for monitoring where we immediately spot where a job or database owner is not DOM\sa (as an example).
PowerShell code to update SQL Server Agent Job Ownership
Function Change-JobOwnersScript {
Param(
[ValidateSet("2008R2","2012","2014","2016")][string]$version
, [ValidateLength(0,100)][string]$server
, [ValidateLength(0,80)][string]$newchange
, [ValidateLength(0,80)][Parameter(Mandatory=$false)][string]$change
)
Process
{
$nl = [Environment]::NewLine
switch ($version)
{
"2008R2" {
Write-Host "Adding SMO library for version $version"
Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
}
"2012" {
Write-Host "Adding SMO library for version $version"
Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
}
"2014" {
Write-Host "Adding SMO library for version $version"
Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
}
"2016" {
Write-Host "Adding SMO library for version $version"
Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
}
}
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server($server)
foreach ($job in $srv.JobServer.Jobs)
{
if ($job.OwnerLoginName -eq $change)
{
$script + "---- Current owner: " + $job.OwnerLoginName + $nl + "EXEC msdb.dbo.sp_update_job @job_id=N'" + $job.JobID + "'" + $nl + ", @owner_login_name=N'$newchange'"
Write-Host $script
}
elseif (($job.OwnerLoginName -ne $newchange) -and ($change -eq ""))
{
$script + "---- Current owner: " + $job.OwnerLoginName + $nl + "EXEC msdb.dbo.sp_update_job @job_id=N'" + $job.JobID + "'" + $nl + ", @owner_login_name=N'$newchange'"
Write-Host $script
}
}
}
}
Change-JobOwnersScript -version 2014 -server "SERVER\INSTANCE" -newchange "DOM\New" -change "sa"
If we’re in an environment where databases and jobs should have multiple owners, we can use the change parameter to pass in the user that it was and the new change parameter will reflect what it should be. Note that the change parameter is not required and in some environments, job owners may be one account, while in others, there may be specific accounts that you need to change. If I use newchange without specifying change, it will script out every job that is not newchangechange, the script will only be generated for jobs that had been owned by it with newchange being the new job owner.
PowerShell code to update SQL Server Database Ownership
Function Change-DBOwnersScript {
Param(
[ValidateSet("2008R2","2012","2014","2016")][string]$version
, [ValidateLength(0,100)][string]$server
, [ValidateLength(0,80)][string]$newchange
, [ValidateLength(0,80)][Parameter(Mandatory=$false)][string]$change
)
Process
{
$nl = [Environment]::NewLine
switch ($version)
{
"2008R2" {
Write-Host "Adding SMO library for version $version"
Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
}
"2012" {
Write-Host "Adding SMO library for version $version"
Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
}
"2014" {
Write-Host "Adding SMO library for version $version"
Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
}
"2016" {
Write-Host "Adding SMO library for version $version"
Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
}
}
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server($server)
foreach ($db in $srv.Databases)
{
if ($db.Owner -eq $change)
{
$script = "USE " + $db.Name + $nl + "EXEC sp_changedbowner @loginame = '$newchange'"
Write-Host $script
}
elseif (($db.Owner -ne $newchange) -and ($change -eq ""))
{
$script + "---- Current owner: " + $job.OwnerLoginName + $nl + "EXEC msdb.dbo.sp_update_job @job_id=N'" + $job.JobID + "'" + $nl + ", @owner_login_name=N'$newchange'"
Write-Host $script
}
}
}
}
Change-DBOwnersScript -version 2014 -server "INSTANCE\SERVER" -newchange "DOM\New" -change "sa"
Customizing Process
While I highly recommend retrieving the information first to verify what is being updated, some options we can run inside the if block instead of writing the script output, including making the change:
### Email what we've found - this also is a saved receipt $body = $server + $nl + $script Send-MailMessage -SmtpServer "" -To "" -From "" -Subject "Job & Database Ownership" -Body $body ### Run the script Invoke-SqlCmd -Query $script -ServerInstance $server -Database "master" ### Save it as a file ### Job ownership: $file = "C:\SavedScripts\" + $job.Name + "_jobowner.sql" $script | Out-File $file ### Database ownership: $file = "C:\SavedScripts\" + $db.Name + "_databaseowner.sql" $script | Out-File $file
Summary
When in doubt, update a user by specific username and avoid updating all jobs based on owner (meaning, always specify a change parameter). Consider that in the case of jobs, you may organize jobs by category relative to owner when an environment has multiple owners – such as maintenance and replication jobs running under one account. Using categories, or comments, can make updating owners simpler as it becomes easy to detect what’s wrong. In addition, you can run these scripts daily – or on a frequent basis – to catch jobs or databases that don’t have an owner that they should.
Next Steps
- If in a multi-user environment, consider a combination of categories and comments for simpler automation of updating information, while retaining scripts.
- Consider running these scripts on a schedule to spot any anomalies with ownership. I’ve caught many “automated” processes that update job and database information with these and it’s helpful to catch early to avoid problems with permissions later.
- Check out these other PowerShell tips.
