Tuesday, March 24, 2015

SSIS Package User should be sa


When you create a maintenance plan job by login your own user name and password, you are locked with that. SQL server agent job will create with that ownership.

----------------------2008--------------------------

Verify if there are any users created any SSIS other than “sa”

select a.name as JobName, b.name as OwnerName from  
msdb..sysssispackages a
inner join Master.dbo.syslogins b
on a.ownersid =  b.sid
   

/* Fix */
UPDATE msdb..sysssispackages
SET     ownersid = 0x01   ---- sa account

WHERE name = 'test'