How to allow non sysadmin accounts to run xp_cmdsheel and execute SSIS packages?

Question

Hi,

We have recently modified our security policy and only 1 DBA can have sysadmin rights on the machine and service and other windows accounts have only minimal rights required for the tasks. We are getting following 2 errors and they are related to user not having sysadmin rights:

1. Error message executing SSIS package from job when both job owner and service account don't have admin rights "Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account"
2. ". The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys' "

I was checking forums and solution mentioned as following for issue 1 and something similar for issue 2:

• creating credentional
• create proxy account
• Associate Proxy with Subsystem
• Grant Permissions to Use Proxy
• Specify Proxy to Use for Job Step

Now when creating credential I as assuming we need to specify the DBA windows username/password for identify/secret since that user only have access to resources, Is this correct assumtion and if yes then we are kind of giving this user almost same permission to execute other tasks and use proxy account as workaround and is there any other approach to fix this issue?

Answer 1

By creating proxy account and running the job with proxy accounts works fine, please give a try.

---

Thanks, Sohail ~Please mark answered if your question is completely answered; vote it as helpful to help others find a solution quicker. This posting is provided AS IS with no warranties.

Answer 2

Hello Harvinder,

Try "execute as" clause: http://msdn.microsoft.com/en-us/library/ms188354(v=sql.105).aspx

Answer 3

Can you give an example on how to use use "execute as" clause when calling SSIS package from job step.

Thanks

--Harvinder

Answer 4

Hi Harvinder,

We can use a SQL Server Agent proxy account. For more detail information, please refer to the following link:

SSIS package does not run when called from a SQL Server Agent job step
http://support.microsoft.com/kb/918760

How to schedule SSIS package to run as something other than SQL Agent Service Account
http://stackoverflow.com/questions/812428/how-to-schedule-ssis-package-to-run-as-something-other-than-sql-agent-service-ac

---

Allen Li
TechNet Community Support