question

Bill Goetschius avatar image
Bill Goetschius asked

SQL Server 2012 -

In SQL Server 2012 - it appears a user needs Administrator priviledge on the Machine to access SSIS packages. Is this true and or is there a way around this?
ssissql-server-2012
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
It completely depends on what you mean by "access" and where the SSIS packages are stored. Since SSIS packages can be exported as files and then called by the engine from shared locations, then no, you don't have to have administrative privileges on the root machine to access SSIS packages. Just access to the storage location. But, if you mean that you've developed and deployed them all on a single box without using the packages files, then you have to administrator rights to the SSIS server, yes. But that can be set up separately from the SQL Server instance. If it's all in MSDB, you can try assigning database owner privileges there. I think that will work.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered
SQL Server supports two formats of packages Deployment: **Package Deployment Mode** In this mode, packages can be deployed to file system and then the access is controlled by file system right or to MSDB. When packages are deployed to MSDB, then when you want to grant access, then you have to create a user in MSDB and then access rights are controlled by **`db_ssisadmin`**, **`db_ssisltduser`** and **`db_ssisoperator`** database roles. More details here: [Integration Services Roles (SSIS Service)][1] **Project Deployment Mode** In this mode the packages are being deployed into SSISDB database and to grant access, you need to create a user in this DB. In this DB, you can assign user to **`ssis_admin`** for administrative access, or you can grant access to individual folders, projects and packages to individual users and/or database roles. You can check for example a post on mssqltips: [Managing SSIS Security with Database Roles][2] [1]: http://technet.microsoft.com/en-us/library/ms141053.aspx [2]: http://www.mssqltips.com/sqlservertip/3153/managing-ssis-security-with-database-roles/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.