CODEWITHSUNDEEP
sql-serversql-server-agent
C-COOP
C-COOP

Reputation: 123

SQL Server Agent permissions versus user permissions

I am setup as an admin on our SQL Server 2012 and I have created a job that runs a procedure that selects records from 2 joined tables. This all works fine when I am logged into SSMS and manually launch the job. The problem is when I try to schedule the job to run (even if I am still logged into SSMS) the same job does not return records from the second table, it is like the join does not work.

I checked and the SQL agent is using the main SQL Server service account which I am assuming would have rights to everything.

Any thoughts on trouble shooting would be appreciated.

Upvotes: 1

Views: 939

Answers (2)

C-COOP
C-COOP

Reputation: 123

Was a corrupt job. I deleted the job and recreated a new one and it worked as expected.

Upvotes: 0

OnoMrBill
OnoMrBill

Reputation: 66

On the Steps page of the sql job you can tell it to "Run As" a particular user.

Otherwise, the account that the SQL Server Agent runs under would have to have rights to all databases/tables, etc.

You should be able to see what's happening when the job runs on a schedule by viewing the job history. You do this by right-clicking either the job in your list of jobs or by right-clicking the job in the Job Monitor and selecting 'View History'.

Hope this helps.

Upvotes: 2

Related Questions