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