Error trying to run SSIS Packages in SQL Server 2008 SQL Server Agent I am trying to run a number of SQL Server 2008 stored procedures that are wrapped in SSIS packages consisting mainly of Execute SQL Tasks. They run fine in Visual Studio 2008 after deploying to a folder on the SQL Server server.
The problem is when I created a job in SQL Server Agent and tried to run the job.  That is when I get the error:

Executing the query &quot;EXEC ? = dbo.usp_Clear_All_JDAWG_Tables&quot; failed with the following error:

&quot;The EXECUTE permission was denied on the object 'usp_Clear_All_JDAWG_Tables' database 'JDAWGD' schema 'dbo'.&quot;.

Possible failure reasons:
Problems with the query
&quot;ResultSet&quot; property not set correctly
parameters not set correctly
or connection not established correctly.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).

The usp_Clear_All_JDAWG_Tables is the name of the first of many packages this job is trying to run.  The stored procs themselves do not use parameters but in the SQL Task I have commands like this to run the stored proc:
EXEC ? = dbo.usp_Load_JDAWG_Tables_From_Persons_All_Agencies

Is there a better/different way to run a stored procedure in the SQL Task?
As a side note, I was able to create and successfully run an Agent job that directly executed the stored procedures that the packages run.
It sounds like your connection manager is setup to use windows Authentication.  If that is the case, when you execute the package via the SQL Server Agent it will run with credentials of the agent.  

You need to do one of three things:


Give the account that the SQL Server Agent runs as appropriate permissions to execute SPs in your database.
Setup a proxy account for the agent with the appropriate permissions (as suggested by Jon Seigel).
Use a different authentication method - i.e SQL Server Auth.


I would go with option 1 if possible, but it depends what your company's policies are.