December 1, 2009 at 3:39 am
Good day
Our server crashed and I managed to recover everything, there is just on thing missing, one of our senior developers could edit/update DTS Packages but defintely was not part of the SYSADMIN ROLE. How can I give him this permission back. I tried to grant execute permissions on the get_dtspackages,add_dtspackages,enum_dtspackages but when editing an existing package when he saves an error pops up,
ERROR: only the owner of .... or members of the sysadmin role are allowed to create new versions of it
This user can create new DTS Package but cannot update existing DTS Package PLEASE ASSIST
December 2, 2009 at 4:11 am
This is totally unsupported by microsoft and you should only make this modification on a development box.
1. Script the sp_add_dtspackage sproc in the msdb database to a file.
2. Edit the code and search for the block of code below.
3. Remark out the following lines as shown below (highlighted in red):
--// We will use the original owner_sid for all new versions - all must have the same owner.
--// New packages will get the current login's SID as owner_sid.
DECLARE @owner_sid VARBINARY(85)
SELECT @owner_sid = MIN(owner_sid) FROM sysdtspackages WHERE id = @id
IF @@rowcount = 0 OR @owner_sid IS NULL
BEGIN
SELECT @owner_sid = SUSER_SID()
END /* 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
*/
4. Drop and recreate the procedure using your new code.
😀 No sysdmin roles needed for them now
December 2, 2009 at 8:31 am
Faiyaaz (12/1/2009)
Good dayOur server crashed and I managed to recover everything, there is just on thing missing, one of our senior developers could edit/update DTS Packages but defintely was not part of the SYSADMIN ROLE. How can I give him this permission back. I tried to grant execute permissions on the get_dtspackages,add_dtspackages,enum_dtspackages but when editing an existing package when he saves an error pops up,
ERROR: only the owner of .... or members of the sysadmin role are allowed to create new versions of it
This user can create new DTS Package but cannot update existing DTS Package PLEASE ASSIST
Did the packages have owner passwords before the server crash? If a package is saved with an owner password, anyone who knows the password can edit, save, and execute the package, even if they're not the owner.
Greg
December 2, 2009 at 10:10 pm
Nope they did not have any passwords on the Packages, but I found a solution thanks alot though
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply