Modifying MSDB system procedures

I personally do not recommend this on production environment but have to do myself to fix the problem of not allowing some users to have sysadmin rights for the sake of creating packages and dropping packages. After a few investigations, I have come up with this idea of what if modifying some of the stored procedures in msdb database that is used to add packages.

I needed to change only two of the system-stored procedures. The two system stored procedures that are modified and the lines commented out are shown below. It just prevents the stored procedure not to check if a user is a member of sysadmin or owner of the package.
1 . sp_add_dtspackage

Commented script
/*
ELSE
BEGIN
--// Only the owner of DTS Package ''%s'' or a member of the sysadmin role may create new versions of it.
IF (@owner_sid <> SUSER_SID() AND (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1))
BEGIN
RAISERROR (14586, -1, -1, @name)
RETURN(1) -- Failure
END
END
*/

2. sp_drop_dtspackage
/*
IF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1)
BEGIN
IF (NOT EXISTS (SELECT * FROM sysdtspackages WHERE id = @id AND owner_sid = SUSER_SID()))
BEGIN
SELECT @name = name FROM sysdtspackages WHERE id = @id
RAISERROR (14587, -1, -1, @name)
RETURN(1) -- Failure
END
END
*/

Once all the changes are saved to msdb database, I have given exec permission to the above stored procedures and it did at least served the purpose. Now, I don’t have to give sysadmin permissions to a user/developer who just need to change the dts package.

Jobs running under

The following sql will help you to find out SQL job name and owner of the job.

select sl.name, j.name from sysjobs j
join master.dbo.syslogins sl
on sl.sid = j.owner_sid