Whilst taking over from the outgoing DBA I wrote this handy query to list the SQL-Jobs he owned …

--list_job_owners.sql
SELECT name job_name, SUSER_SNAME(owner_sid) job_owner
FROM msdb.dbo.SysJobs
ORDER BY name;

… and here’s the code to change them to ‘sa’ …

--change_job_owners.sql
EXEC msdb.dbo.sp_manage_jobs_by_login
    @action = N'REASSIGN',
    @current_owner_login_name = N'SomeLoginName',
    @new_owner_login_name = N'sa';

*UPDATE* Here’s a combination of the two that will change ALL SQL-Job owners to ‘sa’

-- change_all_job_owners_to_sa.sql
declare @cmd varchar(1000)
select @cmd = 'EXEC msdb.dbo.sp_manage_jobs_by_login
@action = N''REASSIGN'',
@current_owner_login_name = N''' + SUSER_SNAME(owner_sid) + ''',
@new_owner_login_name = N''sa'';'
FROM msdb.dbo.SysJobs
exec(@cmd)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s