SSIS job is failing

The SSIS job is failing with the following error

"Executed as user: Domain_name\User_name. ...00.5292.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  8:59:31 AM  Error: 2012-07-17 08:59:33.57     Code: 0x00000004     Source: Error Handling      Description: The Script returned a failure result.  End Error  Error: 2012-07-17 08:59:33.57     Code: 0xC002F210     Source: Log Error Execute SQL Task     Description: Executing the query "insert into SMCubeProcess_Log values ('Table transfer failed in cube processing: Execution failed with the following error: "Enumerate collations failed for Server 'GEDSSQL20D,8900'. ".  ', 'SMCubeDepot', getdate())" failed with the following error: "Incorrect syntax near 'SQL_Server_name'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  End Error  Error: 2012-07-17 08:59:33.58     Code: 0xC002F325     Source: Transfer Tables From...  The package execution fa...  The step failed."

 

 

A SQL profiler was ran against the SSIS package and it was found that there was a function being called by Security Manager that was locked down due to hardening of the SQL server.   Once the account trying to call the function was allowed to execute the SP, the SSIS job ran successfully.

 

User the following link to find examples on how to configure permissions for Stored Procedures

 

E.g. Granting EXECUTE permission on a stored procedure

The following example grants EXECUTE permission on stored procedure HumanResources.uspUpdateEmployeeHireInfo to an application role called Recruiting11.

 

USE AdventureWorks2012;
GRANT EXECUTE ON OBJECT::HumanResources.uspUpdateEmployeeHireInfo
    TO Recruiting11;
GO

 

This code is being called from the context of the runas login for the SSIS job, and is generating the following exception when executing.
 
Msg 229, Level 14, State 5, Procedure sp_tablecollations, Line 1
The EXECUTE permission was denied on the object 'sp_tablecollations', database 'mssqlsystemresource', schema 'sys'.
 
The system stored procedure, sp_tablecollations, is undocumented by Microsoft per the following SQL Books Online article:  System Stored Procedures

In some cases this system object is locked down through “hardening” of the SQL server because it is an undocumented SP.