Last night on twitter an interesting question was asked by @just_samson using the #SQLHelp hash tag. He asked, “can you change the owner for jobs &dbs across instances using Policy Based Management and Central Management Server?” The answer to this question is no because an agent facet doesn’t exist. Users cannot create facets. Does this mean he is dead out of the water? No, because good old T-SQL and CMS can be leveraged to get the job done.
Today we are going to focus on changing SQL Agent Job owners.
How do we find owners?
SELECT sv.name AS [Name], sv.job_id AS [JobID], l.name AS UserName FROM msdb.dbo.sysjobs_view AS sv INNER JOIN [master].[sys].[syslogins] l ON sv.owner_sid = l.sid ORDER BY sv.[Name] ASC
Here is a screen shot from my demo so you can follow along.
How do we change the job owner for all jobs?
You can see that the first job “Device by Zero” has the owner name set to “PBMDEMO\Administrator.” In this example we will want to change the owner to be “sa”. The following script below will do this. The only problem is you would have to run this script against every instance. That’s where the Central Management Server comes into play.
** This script is used as demo. It will only work against SQL 2005\2008\2008R2. Run it on development before you consider using it in production. If you decide to run it in production you are on your own. Run it at your own risk. **
DECLARE @JobID uniqueidentifier DECLARE @NewOwner varchar(200) DECLARE @OldName varchar(200) SET @NewOwner = 'sa' SET @OldName = 'PBMDEMO\Administrator' SELECT sv.name AS [Name], sv.job_id AS [JobID], l.name AS [OwnerName] INTO #SQLJobs FROM msdb.dbo.sysjobs_view AS sv INNER JOIN [master].[sys].[syslogins] l ON sv.owner_sid = l.sid WHERE l.name like @OldName ORDER BY sv.[Name] ASC SELECT * FROM #SQLJobs WHILE (SELECT COUNT(*) FROM #SQLJobs ) > 0 BEGIN SELECT TOP 1 @JobID = JobID FROM #SQLJobs EXEC msdb.dbo.sp_update_job @job_id= @JobID, @owner_login_name=@NewOwner DELETE FROM #SQLJobs WHERE JobID = @JobID END DROP TABLE #SQLJobs
Now assuming you have Central Management Server configured just right click on the group of servers you want the change to be applied to and select “New Query.” Copy and paste the code and you should be able to execute the query. Below are some screenshots from my demo incase you are new to Central Management Server. We will apply the script against the Production group.
Finally if we execute the initial query we will see that all jobs have “sa” as the owner.