The other day I was faced with the task of giving a user full access to their own jobs wich run SSIS packages, while (of course) avoiding to make them a member of the sysadmin server role.
The server version was SQL Server 2008. It turned out to be a not so trivial task as you would expect. The steps involved are as follows:
1. Create a windows login for the person (if not already in place)
2. Grant that login membership in the following roles in MSDB: db_ssisltduser, SQLAgentUserRole
3. Create a Credential för that login
4. Create a SSIS proxy using the Credential and Login (Remeber to tick the SSIS subsystem box)
5. Create the job and its steps
6. Set the login as owner of the job
7. Configure the SSIS jobstep(s) to runs as the Proxy account Easy? Well, not really. But it works.