Hi experts, I am granding group of users to import ssis packages from development to production. I granted db_dtsltduser role in msdb. Users can import package from dev to prod. But problem is that they can also delete packages which we dont want. Is there a way that users should just import packages but not able to delete? I tried to revoke execute on sp_dts_deletepackage but no vain. Any help will be appreciated. Thanks

I've looked into this a couple of times and gave up due to limitations and frustration. In the end I just assigned db_dtsltduser role as this gives users permissions to delete their own packages but not other peoples packages. If you want to stop people deleting packages, you need to create a custom role, add the sp_dts_deletepackage (sp_ssis_deletepackage for SSIS 2008) securable to the role, set DENY permissions on EXECUTE sp_dts_deletepackage (sp_ssis_deletepackage for SSIS 2008), add users to the role and then assign this role to packages individually. Just adding users to the role does not prevent them from deleting packages (from what I've read and tried) which is the stupid part. Here is a link that shows how to create and assign custom roles to packages. http://synsol.eu/blog/2010/02/how-to-give-permissions-on-a-windows-security-group-to-an-ssis-package/ Would be great to see if someone has been able to achieve this without the need to assign the role to packages individually :-) Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD

Hi, you can set deny Delete permission for a specific user. Thanks, Ayyappan http://sqlserverrider.blogspot.com

Hi, you can set deny Delete permission for a specific user. Thanks, Ayyappan http://sqlserverrider.blogspot.com Can you please provide an example of how you do this in SSIS as I believe you are wrong, especially when the permissions need to be assigned to each package.Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD

Thanks Jeff and Ayyapan for reply. I think Jeff is right. There is no other way to do it. I was thinking to remove execute permission from this role on sp_dts_deletepackage. In this way user will not able to delete package but this will also affect other users where this role assign. I am trying to create custom role and then assing required sp. If anyone else done this please share your experience. Thanks

I believe you can create a separate group(say DenyGroup) with the users who you want to deny the permissions on package. Deny permission to DenyGroup on package - deny will take the precedence. One deny stop doing even you have 100 grantshttp://uk.linkedin.com/in/ramjaddu