How to give permissions on a windows security group to an SSIS package

By tom on February 2nd, 2010

Recently I had to search for a mechanism to give certain people permissions to certain SSIS packages. By default only the package creator and the group db_dtsadmin have writer permissions and the package creator and the groups db_dtsadmin and db_dtsoperator have read permissions.
Turns out that there is no easy way to do this and that you have to go through some steps in order to achieve this, so let’s get started.

First thing to do is create a custom role in msdb:

  • Log on to your SQL Server Instance
  • Go to Databases –> System Databases –> msdb – Security –> Roles –> Database Roles
  • Right-click on Database Roles and select New Database Role
  • Create a new role, you don’t have to change the owned schemas

 

Then we need to assign permissions to our security group:

  • Go to Security –> Logins
    Either create a new login for the user group or select an existing login
  • Select User Mappings –> Select Map for msdb and assign following role memberships:
    db_dtsltduser (if you forget this one, you will get an access denied on the stored proc that is used to retreive a list of SSIS packages already installed)
    public (already selected)
    SSIS_Package_PACKAGENAME_Admins (or the role name you previously created)

The last step is to change the permissions of the SSIS packages

  • Open SQL Server Management Studio, connect to the Integration Server instance
  • Go to Stored Packages –> MSDB –> Name of the package that you want to change permissions –> Right-click and select Package Roles
  • Adapt the Reader and/or Writer Role to the role that you previously created

 

That’s it.

Remark: you might also need to give your security group remote access to your SSIS. The  procedure on MSDN can be found here.

Hope this helps,
Tom