Rights to create DTS package

Question

Rights to create DTS package

vladimir.antovic

SSC Veteran

Points: 202
More actions
November 5, 2008 at 6:08 am

#75353

What user rigths are needed to create a DTS package in SQL Server 2000?

Viewing 9 posts - 1 through 8 (of 8 total)

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

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 1

You can create packages with just public role membership but for defining tasks under package you need to make connections to server and access the intended database. For accessing a particular database and to perform some operation under that database you need permissions based on what action you really want to perform. For example, db_reader role membershipr for just reading the content in tables under that database.

HTH,

MJ

vladimir.antovic SSC Veteran Points: 202 More actions · Answer 2

Thank you for the answer,

but how then you can forbid developers to create DTS package on the production server?

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 3

They can only do something using dts once they get database access, you need to provide only the required access and they will be controlled automatically.

MJ

tosscrosby SSCoach Points: 18045 More actions · Answer 4

Never give your developers access to your production servers. That should be your responsibility. They develop and unit test. Somebody (you??) does a final test and promotes to production, period. You're asking for a world of trouble by allowing to much random access to your production box - IMO.

Edit - NEVER!

-- You can't be late until you show up.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 5

I was just telling him how dts package can be used once created. Only users with access to database can manipulate data from dts.

I never intended to provide direct access to production. We shldn't do tht I agree with you.

MJ

tosscrosby SSCoach Points: 18045 More actions · Answer 6

vladimir.antovic (11/6/2008)
Thank you for the answer,
but how then you can forbid developers to create DTS package on the production server?

Soory, I should've quoted this in my first post. The point I was trying to make is that there is nothing to forbid if they don't have access in the first place.

-- You can't be late until you show up.

Calvin Duffy SSCarpal Tunnel Points: 4246 More actions · Answer 7

Deny execute permission on the "sp_add_dtspackage" stored procedure in msdb database. They can still create DTS packages but NOT save it on the server. They can save it as a file only..........This is what I am doing............They can also edit the existing DTS packages on the server (msdb database) but NOT save the changes.

Calvin.

vladimir.antovic SSC Veteran Points: 202 More actions · Answer 8

Thank you Calvin.

I did that, and it's OK.

I, also, deny rights on "sp_get_dtspackage " and "sp_drop_dtspackage".

That way users cannot change or drop DTS package.