Monthly Archives: February 2019

Change job owner for Agent jobs

create procedure dbo.sp_change_job_owner
@jobName varchar(500),
@ownername varchar(60)
AS

DECLARE @owner_sid varbinary(85)

Select @owner_sid= sid
FROM master.sys.syslogins(NOLOCK)
WHERE name like @ownername

UPDATE msdb.dbo.sysjobs set owner_sid = @owner_sid
where name like @jobName

UPDATE msdb.dbo.sysschedules SET msdb.dbo.sysschedules.owner_sid= @owner_sid
FROM msdb.dbo.sysjobs job
inner join msdb.dbo.sysjobschedules jobsch on job.job_id = jobsch.job_id
INNER JOIN msdb.dbo.sysschedules sch on sch.schedule_id = jobsch.schedule_id
WHERE job.name like @jobName

SQL Server Replication — Adding new articles to existing publishcation

  1. Add one or more articles through one of the following methods:

  2. After adding an article to a publication, you must create a new snapshot for the publication (and all partitions if it is a merge publication with parameterized filters). The Distribution Agent or Merge Agent then copies the schema and data for the new article to the Subscriber (it does not reinitialize the entire publication).
  3. After the snapshot is created, synchronize the subscription to copy the schema and data for the new article.