We are trying to get SQL Server set up so that non-admin end-users can own and execute jobs that execute SSIS packages. While I understand what I'm about to describe will not pass a good security audit, at this point we're just trying to get it working.
There are three domain accounts involved here:
1. The SQL Agent account: ABC\SQLAgent
2. A domain user account with domain admin priviledges: ABC\Dave
3. A domain user account that is basically just a member of Domain Users, ABC\Mary.
The idea here is that we want Mary to be able to own and run a job, but run it as though she were impersonating Dave. Here's what we've attempted so far and the error we are receiving.
1. Add the domain account ABC\Mary to the SQL Server instance.
2. Map ABC\Mary to the msdb database with the default schema of 'dbo' and membership in the 'SQLAgentUserRole' database role.
3. Create a credential called 'Dave's Credentials' using the identity of ABC\Dave and specify the domain password.
4. Map Mary's account the the credential 'Dave's Credential'.
5. Create a SQL Agent Proxy called 'Proxy via Dave' using the credentials 'Dave's Credentials'. Add the proxy to the
SSIS subsystem.
6. Create a SQL Agent Job owned by ABC\Mary containing one job step that executes an SSIS package.
View Complete Post