permissionssql serverssis
I'm quite new to SSIS.
Is it a straightforward to grant SSIS permissions in the AD environment so that that a user can only create/manage/run ETL tasks on specific databases/Analysis Services projects on a SQL Server that contains multiple databases?
I.e. So a user can't extract/load data from/into a database/Analysis services project that they shouldn't etc.
Database version is SQL Server 2012.
Thanks!
I would suggest reading up on SSIS at MSDN: Initial Installation (Integration Services) -this references having a standalone installat of just SSIS.
Quote from above link:
SQL Server Integration Services is installed through the SQL Server 2008 Setup program. You can install Integration Services alongside other SQL Server components or you can install Integration Services as a stand-alone component. Integration Services includes both client and server applications.
To use a dedicated server for extraction, transformation, and loading (ETL) processes, we recommend that you install a local instance of the SQL Server Database Engine when you install Integration Services. Integration Services typically stores packages in an instance of the Database Engine and relies on SQL Server Agent for scheduling those packages. If the ETL server does not have an instance of the Database Engine, you will have to schedule or run packages from a server that does have an instance of the Database Engine. This means that the packages will not be running on the ETL server, but instead on the server from which they were started. As a result, the resources of the dedicated ETL server are not being used as intended. Furthemore, the resources of other servers might be strained by the running ETL processes.
Some additional information could be found here as well: Considerations for Installing Integration Services. You might want to take under consideration the licensing as well and still validate the standalone server is licensed properly.
Permissions to assign her are going to be based on what deployment model she is going to use within your environment. SSIS 2012/2014 added an additional model: Project and Package
For Package Deployment model she has the option of deploying the packages to msdb. Granting her permissions for this type of deployment you will grant her permissions to an SSIS role. If she is the only person that is going to be working with the packages you can simply grant her db_ssisltduser. This will provide her permissions to do anything she needs with the packages she creates and deploys (making her the owner of those packages). You can read through the article linked to get an idea of more.
For Project Deployment model you will need to work with the SSISDB catalog. With this it is just granting permissions at a database-level role. If this is not already created on the instance you will need to do this for her. Once it is created you should see a database that will have a specific role called ssis_admin, this role is special to the catalog database and is not a normal role found in every database.
The database will show up under the Databases node in Object Explorer, just go to Security>Roles>Database Roles and find the ssis_admin role. Within the properties of that role you can add her login.
Best Answer
If you intend to run the SSIS jobs from the SQL Agent, then they will execute as the user the agent runs as. That's usually the same as the account the instance runs as, which has rights to everything.