Permission for non-sysadmins to create and manage SSIS packages and run it from SQL Agent

There are different ways to manage permissions in SQL server environment. As a DBA, I came across different environments, where everyone got sysadmin access. Trust me, I'm not exaggerating here. 

I recently worked for a client where they are using an AD group for all the developers and that AD group got sysadmin access in all the environments. As you know, it's a RISK for the environment and of course, for the DBA. When someone hired you as their DBA, then it's your responsibility to protect the environment as long as you are their DBA!

Normally when a DBA asks reason for sysadmin privilege, normal answers are like yeah I need to develop SSIS packages, then create jobs and run those jobs etc.

So how to grand access for a developer to do all the required jobs like add a SSIS catalog, modify, create a SQL Agent job, run and modify without waiting for the DBA time? 

Just a side note, always try to give permissions through groups instead of adding individual users in the server. It's not mandatory; but you will appreciate it when the environment starts growing.

Adding permissions to manage Integration Service Catalog

You can either give full access to the catalog or restrict access in folder level. 

For full access, 

1. Create login under Security (image below)
2. Go to User Mapping and select SSISDB database.
3. Adding ssis_admin role give the user to do all the tasks in the catalog. I'm not elaborating different roles here. But you can try it yourself or search online for different options.

For folder level access,

Follow steps 1 & 2 as above and don't choose any roles other than public (same like the above screenshot)

Then go to the Integration Services Catalogs section and right-click the folder we intend to give access. Then select the Permissions window and add the required permissions (image below).

That's all for giving access to create or edit SSIS catalogs!

Next part is how to give access to SQL Agent jobs to add a job and run it?

For SQL Agent access, msdb is the database we need to concentrate (image below). There are 3 roles you need to use and give access as required. As I mentioned earlier, I'm not elaborating each roles here.

Above configuration will give access for the user to see and execute the jobs. But it won't give all the necessary permissions to create jobs especially when calling SSIS packages as it required higher permissions to run the package. You may get errors like below.

Here the trick is create a proxy account to run the job and grant access for the user to the proxy account. Don't forget to add all the required permissions for the proxy account to do the job.

You can right-click the proxy account and add the user under Principals.

Hope this will help you to solve the permission without giving sys-admin permissions for the individual user. If you have any questions, please comment below. 

Happy coding!