How exactly does SSIS security work? I thought the reason why db_dtsltduser,
db_dtsoperator have read access to a package is because they are by default
assigned the Reader Role on the package. But if I create my own database role
in msdb, call it MyUsers, then replace the existing <default: db_dtsadmin,
db_dtsoperator, creator > with the role I just created, anyone in that role
has no access to the package. I have to, in addition, place any users in
MyUsers role either in db_dtsoperator or db_dtsadmin before they can read the
package. What's going on here? There appears to be something more special
about db_dtsadmin, db_dtsltduser and db_dtsoperator that is more than just
being database roles. What is it?

Charles Kangai, MCT, MCDBA

I see. It's all to do with these msdb stored procedures that db_dtsltduser,
db_dtsoperator and db_dtsadmin have prior permissions on:

sp_dts_getfolder
sp_dts_addfolder
sp_dts_deletefolder
sp_dts_renamefolder
sp_dts_listfolders
sp_dts_getpackage
sp_dts_putpackage
sp_dts_deletepackage
sp_dts_listpackages
sp_dts_getpackageroles
sp_dts_setpackageroles

Permissions are required to execute these stored procedures, and additional
permissions are required on the package itself. I found out from this article
by Kirk Haselden at sqlmag.com (BOL is virtually useless on this topic, as on
a few other topics):
http://www.sqlmag.com/Articles/Artic...23/pg/1/1.html

Charles Kangai, MCT, MCDBA


"Charles Kangai" wrote: