CODEWITHSUNDEEP
sql-serverstored-procedurespermissionsjobs
user2376230
user2376230

Reputation: 33

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.

Upvotes: 0

Views: 1420

Answers (2)

user2376230
user2376230

Reputation: 33

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.

Upvotes: 0

Jericho
Jericho

Reputation: 213

As you mentioned this sounds like a permission issue - you are able to run it fine in SSMS because your login (presumably) has access to both servers, but the default SQL Agent Server login doesn't have access to the linked server - so it can run the Stored Procedure but with no access to the linked server it returns no data.

I'd take a look into creating a SQL Server Agent Proxy account - this allows you to use a login for a SQL Job that both has permission to run the stored procedure as well as access to the linked server. Just make sure that whatever login you use has the correct access on both servers.

Here's the MS TechNet link on how to create a proxy account:

https://technet.microsoft.com/en-us/library/ms190698(v=sql.105).aspx

Upvotes: 0

Related Questions