February 18, 2009 at 10:15 am
Hello Everyone,
I have developers working in a development environment creating and saving DTS packages. Due to project allocation, there is often the need for one developer to edit a DTS package created by a different developer. As a security measure, we never allow anyone outside of our DBA's to reside in the sysadmin role. How can a developer who is neither in the sysadmin role or the owner of the DTS package edit that package? There has to be another workaround besides adding the developer to the sysadmin role.
Thanks
February 18, 2009 at 10:53 am
Stored procedure msdb.dbo.sp_reassign_dtspackageowner can be used to change the owner of a DTS package but MS has hard-code logic to restrict the usage only to the current package owner or a sysadmin .
Suggest creating a new procedure based on sp_reassign_dtspackageowner but that does not include the hard-coded security logic and then grant execute rights appropriately.
These are the SQL statements that need to be excluded:
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 (14585, -1, -1, @name)
RETURN(1) -- Failure
END
END
SQL = Scarcely Qualifies as a Language
February 18, 2009 at 1:08 pm
If the packages are saved with an owner password set, anyone who knows the password can edit, save, and run the package. See "Handling Package Security in DTS" in BOL.
This is how we share maintenance of packages among a group of people.
Greg
February 18, 2009 at 2:09 pm
Thanks for the suggestion! I copied the sp and made the necessary security adjustments removing the check for ownership and sysadmin membership. I added a security check to verify the user was a member of db_dtsadmin just for some sort of security handling.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply