0

I'm new to a SSIS workflow and I'm trying to understand how should I go about grating permissions.

There are many different database-level roles for SSIS, there is SQL Server Agent involved or Proxies. I don't know when to use what.

At this point I'm pretty much lost and even worse I feel like there is a mess in the Microsoft documentation. I will try my best explaining what I don't know but at this point there is complete chaos in my head.


I need to figure out two permissions level:

  • permissions for a developer who will develop and deploy the application
  • permissions for an application that will later perform operation on the databases.

I don't know if I should consider separate permission level specifically for managing/reading from SSIS etc.?


My first though was to use database roles. I know there are some database-level roles in two separate databases:

  • msdb
  • SSISDB

Both of them have some database-level roles concerning SSIS. What I've gathered is that msdb was used for that purpose in the past and not anymore - now recommended way is to use only SSISDB. So I should not be concerned about SSIS roles inside msdb (like db_ssisadmin etc.). Correct?

Meanwhile inside SSISDB there are 5 roles:

  • ssis_admin
  • ssis_cluster_executor
  • ssis_cluster_worker
  • ssis_failover_monitoring_agent
  • ssis_logreader

Of which only 2 are mentioned inside Microsoft documentation (ssis_admin, ssis_logreader): https://docs.microsoft.com/en-us/sql/integration-services/security/integration-services-roles-ssis-service?view=sql-server-ver15

What about ssis_cluster_executor and ssis_cluster_worker? What is the use of this roles? Should we use them?


Apart of SSIS roles I have problem with SQL Agent and its permissions. Inside msdb there are 3 roles:

  • SQLAgentUserRole
  • SQLAgentReaderRole
  • SQLAgentOperatorRole

I've read that SSIS uses SQL Agent if so - should I also assign its roles as well as SSIS roles or this is completely separate thing?


Then I stumbled across proxies and its use in grating permissions to SSIS.

When should we use them? Are they replacement for database-level roles or they should be used in combination with roles? I'm talking about connecting two SQL Server databases here. And we can assume there is a login that has access to both of the databases.

Can I use SSIS similarly to Linked Server where we could current security context?


Currently we are in the process of building windows domain for database servers and we are rebuilding our old permission structure. We are moving from logins with SQL authentication over to logins with Windows domain authentication.

Whole point of our new structure was to control database access from Active Directory and I feel like SSIS with proxies does not fit that approach.

Any clarification much appreciated and sorry for this chaotic question.

1 Answer 1

1

The first question is whether you are comfortable having all Agent jobs and SSIS packages run as a system administrator. This is, by far, the most common and simplest model. If you're happy having an admin run all jobs, you have a sysadmin (production DBA) deploy all jobs and packages and run them as the SQL Agent service account.

The same goes for application deployment. The simplest security model is for a DBA to perform all production deployments.

If you want the developers to manage the deployments, and your production SQL Server hosts multiple applications, then you typically grant CONROL/DB_OWNER permissions for each application on its own database. In this model you may need to grant SQLAgentUserRole for the users who deploy code. This enables a user to deploy and manage their own jobs.

Not the answer you're looking for? Browse other questions tagged or ask your own question.