Rotating Header Image

Running SSIS Packages – Security

So as most of my blog articles this post is inspired by an email I received asking for permissions to run SSIS packages. 

In SQL Server 2005 there are 3 Database Roles in the msdb system database:

MSDN Integration Services Roles 2005

Role Read action Write action
db_dtsadmin Enumerate own packages. Import packages.
or Enumerate all packages. Delete own packages.
sysadmin View own packages. Delete all packages.
  View all packages. Change own package roles.
  Execute own packages. Change all package roles.
  Execute all packages.  
  Export own packages.  
  Export all packages.  
  Execute all packages in SQL Server Agent.  
db_dtsltduser Enumerate own packages. Import packages.
Enumerate all packages. Delete own packages.
View own packages. Change own package roles.
Execute own packages.  
Export own packages.  
db_dtsoperator Enumerate all packages. None
View all packages.
Execute all packages.
Export all packages.
Execute all packages in SQL Server Agent.
Windows administrators View execution details of all running packages. Stop all currently running packages.

 
In SQL Server 2008 these 3 Database Roles are now named db_ssisltduser, db_ssisoperator, and db_ssisadmin

 MSDN Integration Services Roles (2008)

Role Read action Write action
db_ssisadmin Enumerate own packages. Import packages.
or Enumerate all packages. Delete own packages.
sysadmin View own packages. Delete all packages.
  View all packages. Change own package roles.
  Execute own packages. Change all package roles.
  Execute all packages. Bitmap
  Export own packages.  
  Export all packages.  
  Execute all packages in SQL Server Agent.  
db_ssisltduser Enumerate own packages. Import packages.
Enumerate all packages. Delete own packages.
View own packages. Change own package roles.
Execute own packages.  
Export own packages.  
db_ssisoperator Enumerate all packages. None
View all packages.
Execute all packages.
Export all packages.
Execute all packages in SQL Server Agent.
Windows administrators View execution details of all running packages. Stop all currently running packages

So in this particular case granting the user db_dtsltduser was enough to fullfill their request as they only needed the ability to run packages that they created.  As always with security/permissions make sure you only grant what’s required instead of going to easier route of using an “admin” type of role.

Enjoy!!

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

6 Comments

  1. Anand says:

    Hi Everyone,

    I am getting one typical error while running SSIS package. I am exporting the data from SQLServer Database Table to Text file. The textfile is in the remote location(\\192.168.0.99\SSIS\Tabledata.txt).
    for windows Authentication or SQL User I gave the db_Owner in master and msdb database. I am able to export the data without any errors.

    for that users, I added SYSADMIN Server Role, from that user when I am trying to run the SSIS package from Management studio, it is giving error.

    Error Number is: Code: 0xC001401E

    Could you please explain anyone…Its very urgent

  2. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=87632

    sounds like it might not be a “SQL” permission issue but rather a file or folder permission issue.

    So you when running the package have permissions to the file or folder but what about the user that will be running the package?

    Hope this helps and sorry that I don’t have more info/ideas on this.

  3. Chris Trout says:

    Dude!

    I’ve been messing around with SQLAgent* roles for the last three hours. Your post enumerated everything that I needed to know…

    My SSIS packages are running properly again!
    Thank you. thank you. thank you.

  4. glad to help and thanks for reading!! 🙂

  5. With iOS 9 and OS X El Capitan it appears Apple is Sherlocking once again with the introductions of the all new Notes app. There are many who have said that the new Notes will be an Evernote killer.

  6. What’s changed this year is how much storage you’re getting for that money. Instead of jumping only 48GB, you now jump a whopping 96GB.

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.