DTS permission
Hi all, I want to grant permission to user to be able to update the DTS without be a memeber in SYSADMIN group!!!any clue SQL Server 2000 SP4 Thanks in advance
December 8th, 2010 11:29am

You need to hack the stored procedures and remove the explicit checks in the code, there is no grantable permission, other than being a sysadmin! Some info... When saving a local package Enterprise Manager calls sp_add_dtspackage found in the msdb database. If you are not the owner and not a member of the sysadmin role an error will be returned. If you fail this check the error "Only the owner of DTS Package 'MyPackage' or a member of the sysadmin role may create new versions of it." will be returned. A similar check is contained within sp_drop_dtspackage which is used when deleting a package. If multiple non-sysadmin developers all need to work on the same package this limitation can cause major problems. One totally unsupported workaround would be to remove the checks from the stored procedure. I have implemented this successfully in development environments where teams all need to work with the same packages, without any problems.http://www.sqlis.com | http://www.konesans.com
Free Windows Admin Tool Kit Click here and download it now
December 8th, 2010 12:36pm

They must have permissions. However by default I think the public role has pretty wide access to msdb. Deny permissions on the procedures will stop people of couse. Take a step back or a moment and think about your overall requirements. It you can state them cleatly perhaps we can adreess them in one go. If you want to audit stuff, then change the procedure to write to an audit table. If you want to control users via group, then use permisions, or even your own explcit role check similar to the sysadmin check you removed.http://www.sqlis.com | http://www.konesans.com
December 9th, 2010 7:05am

You need to hack the stored procedures and remove the explicit checks in the code, there is no grantable permission, other than being a sysadmin! Some info... When saving a local package Enterprise Manager calls sp_add_dtspackage found in the msdb database. If you are not the owner and not a member of the sysadmin role an error will be returned. If you fail this check the error "Only the owner of DTS Package 'MyPackage' or a member of the sysadmin role may create new versions of it." will be returned. A similar check is contained within sp_drop_dtspackage which is used when deleting a package. If multiple non-sysadmin developers all need to work on the same package this limitation can cause major problems. One totally unsupported workaround would be to remove the checks from the stored procedure. I have implemented this successfully in development environments where teams all need to work with the same packages, without any problems. http://www.sqlis.com | http://www.konesans.co I tried above solutions but i have to delete the Owner_SID also from the insert statement at the end of the SP and can't trace who made the last update for the packge is there any suggest solution thanks in advance
Free Windows Admin Tool Kit Click here and download it now
December 9th, 2010 7:42am

Why did you have to remove that column?http://www.sqlis.com | http://www.konesans.com
December 9th, 2010 7:55am

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 I'll guess that you deleted the entire block illustrated above. Not surprisingly you lose the declaration of @owner_sid, but why not change it to something like below, or a variation therefore- 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 http://www.sqlis.com | http://www.konesans.com
Free Windows Admin Tool Kit Click here and download it now
December 9th, 2010 8:00am

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 I'll guess that you deleted the entire block illustrated above. Not surprisingly you lose the declaration of @owner_sid, but why not change it to something like below, or a variation therefore- 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 http://www.sqlis.com | http://www.konesans.com Great ...but I am wonder why any user can update the DTS even who don't have permission on msdb..is there way to audit the updates who done what.. thanks
December 9th, 2010 8:46am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics