Wednesday, December 06, 2006

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.

2 comments:

Anonymous said...

These 2 system stored procedures are greyed out when viewing them and do not allow them to be changed. I have one server set up this way already from years ago - but having a problem getting a 'user' defined stored procedure out there for 'sp_add_dtspackage' to comment out the scripts. Any idea on how to allow changes to these 'read-only' system stored procedures? I have SQL admin rights? Thanks!

Anonymous said...

Have you tried to use sp_configure with override options ?