DTS Package Security

Question

DTS Package Security

ramses2nd

Hall of Fame

Points: 3720
More actions
March 7, 2005 at 8:38 am

#92621

I know you need to be a member of the sysadmin group to update a DTS package not owned by you. Since when one of the developers try this they recieve the error "Only the owner of dts package "blah blah" or a member of the syadmin roll can create new versions of it" Is there a way around this that does not involve adding them to the sysadmin role?

Any Help would be appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

You could put an owner password on the package and tell the developer what the password is. To add the password you'll have to do a "save as" and give it a new name along with the owner password.

Greg

ramses2nd Hall of Fame Points: 3720 More actions · Answer 2

Yeah, I thought of that but it still does not work.

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

Try adding the developer to the TargetServersRole role in MSDB and grant EXECUTE permission on sp_add_dtspackage and sp_get_dtspackage to TargetServersRole. You could also create a custom user role in MSDB if you don't want to mess with TargetServersRole.

Greg

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 4

TargetServersRole is not needed to handle creation/modification of DTS packages. In msdb the TargetServers role doesn't have any explicit permissions not already assigned to public. It does, however, have the ability to view on jobs through the view sysjobs_view and is needed to manage jobs if the user isn't a sysadmin.

K. Brian Kelley
@kbriankelley

Greg Charles SSC-Forever Points: 45403 More actions · Answer 5

Aah, you're right, of course, Brian. It just happened that TargetServersRole had been granted permissions to execute the DTS stored procedures on the server I looked at.

Greg