Search code examples
sql-serverstored-procedurespermissionsjobs

SQL Linked Server returns no rows when ran via SQL Job

I have a TSQL stored procedure that runs just fine when I execute via SSMS query window. This stored procedure utilizes a linked server.

However, when I run the stored procedure via SQL Job, whether it's on-demand or scheduled, it returns 0 results (all the selects are from the linked server). The job DOES run, however, because the first line in the SP truncates the table. And when I view the Job History, it says it's successful.

I'm guessing this is a permissions issue but I don't know what to do. Any help would be appreciated.

Solution

  • The issue was due to the SQL Agent not having permissions to Dynamics CRM 2011 filtered views. We will either bypass the filtered views and hit the direct tables, change the linked server properties to a CRM user that has permission, or create a CRM user for the SQL Agent user.