June 20, 2012 at 3:11 pm
In reviewing our SQL instances, we find several SQLAgent jobs where the owner is someone's Windows account (myself or one of my colleagues) rather than a service account, sa or other SQL account. This is because the job owner defaults to the Login used while creating the job.
Is there a way to set a default job owner, or do we have remember to manually set the job owner if we're not using the login we want as owner when creating the job?
June 20, 2012 at 8:40 pm
No, you cannot change who the default owner is of a job. When you create a SQL Server Agent job, the msdb.dbo.sp_add_job stored procedure is what is called. Looking at BOL's reference on sp_add_job, see the following description for the @owner_login_name parameter:
[ @owner_login_name = ] 'login'
The name of the login that owns the job. loginis sysname, with a default of NULL, which is interpreted as the current login name. Only members of the sysadmin fixed server role can set or change the value for @owner_login_name. If users who are not members of the sysadmin role set or change the value of @owner_login_name, execution of this stored procedure fails and an error is returned.
June 20, 2012 at 11:15 pm
I wrote a script to do this a long time ago. It is flexible so you can specify an old and/or new owner and it can change only jobs owned by specific people and/or change it to any person. If you don't specify a new owner, it will default to the sa account/ If you don't specify an old owner, it will default to all jobs not owned by the new owner (or sa).
If you don't specify either owner, it will default to changing all jobs not owned by sa to be owned by sa.
It also has a @debug parameter which is set by default. When set to 1, the script will simply output the SQL code for you to run manually. If @debug = 0 it will make the changes itself.
Declare @JobID uniqueidentifier,
@JobName sysname,
@OldOwner sysname,
@NewOwner sysname,
@MaxID int,
@CurrID int,
@sql nvarchar(1000),
@Debug bit
Declare @Jobs Table (JobID int identity(1, 1) not null primary key,
Job_ID uniqueidentifier not null,
JobName sysname not null)
-- Customize old and new owner
Set @OldOwner = '';
Set @NewOwner = '';
-- @Debug: 0 = Execute, 1 = Output code without executing
Set @Debug = 1;
If @NewOwner = '' Set @NewOwner = 'sa';
If Exists (Select 1 From msdb.dbo.sysjobs J
Inner Join sys.server_principals SP On SP.sid = J.owner_sid
Where SP.name = @OldOwner
Or (@OldOwner = ''
And SP.name <> @NewOwner))
Begin
If Exists (Select 1 From sys.server_principals
Where name = @NewOwner)
Begin
Insert Into @Jobs (Job_ID, JobName)
Select J.job_id, J.name
From msdb.dbo.sysjobs J
Inner Join sys.server_principals SP On SP.sid = J.owner_sid
Where SP.name = @OldOwner
Or (@OldOwner = ''
And SP.name <> @NewOwner);
Select @MaxID = Max(JobID), @CurrID = 1
From @Jobs;
While @CurrID <= @MaxID
Begin
Select @JobID = Job_ID, @JobName = JobName
From @Jobs
Where JobID = @CurrID;
If @Debug = 1
Begin
Print 'Exec msdb..sp_update_job';
Print char(9) + '@job_id = ''' + Cast(@JobID as nvarchar(50)) + ''',';
Print char(9) + '@owner_login_name = ''' + @NewOwner + ''';';
Print 'GO' + char(10);
End
Else
Begin
Print 'Updating owner on job: ' + @JobName;
Exec msdb..sp_update_job @job_id = @JobID,
@owner_login_name = @NewOwner;
End
Set @CurrID = @CurrID + 1;
End
End
Else
Begin
RaisError('Login [%s] does not exist', 1, 1, @NewOwner);
End
End
Else
Begin
Print 'No jobs are owned by [' + @OldOwner + ']';
End
Go
June 21, 2012 at 9:17 am
Thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply