Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account

When I tried to run the integration package through SQL Server Agent Job in the client environment, it failed with error message "Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account.". However, while run the package from Integration Service, it ran successfully.  As we can’t request “sysadmin” permission from client’s for production environment, there should be alternative way to solve this problem.

The error message is clearly telling the solution to this problem that is create a proxy account and run the job by that proxy account, so I did the same the problem was solved. As this proxy account was new to me, I am writing this blog about what is proxy account? And How to create it? Some more details about it.

What is SQL Server proxy account?

A SQL Server proxy account defines a security context in which a job step can run. Each proxy corresponds to a security credential. To set permissions for a particular job step, create a proxy that has the required permissions for a SQL Server Agent subsystem, and then assign that proxy to the job step. To get further information in technet site such Limitations and Restrictions and Security.

You must create a credential before you create a proxy if one is not already available. The below images show that steps that to create credential for my windows user account.

Once you click on New Credential this will pop up the below screen, give any name for the credential and select the existing user, any password and then press ok. This will create the credential as shown below.

Once you create the credential, create a new proxy account under SQL Server Agent as shown below.

In the New Proxy Account Window, give proxy account name as you want, select the credential and select the relevant activity that need to perform by this proxy account.

Here I have selected 3 activities that are why the proxy account will be under the activity subfolders.

Once you create done this, while creating the Job step, just select the particular proxy account for “Run as”.

Cheers!

Uma