1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
C-COOP
  • 123
  • 1
  • 3
  • 12
  • Any hints in the job history? – artm Feb 21 '17 at 22:56
  • @artm, no error message because the job runs fine - the data is just incomplete. If I manually start the job from SSMS I get data from both tables, if I schedule it ignores the join and only returns half the data. – C-COOP Feb 21 '17 at 23:21

2 Answers2

2

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.

OnoMrBill
  • 56
  • 4
  • When I manually run the procedure with no RunAs - no issue. When I manually run the job (that runs the same procedure) with no RunAs - no issue. When I schedule the job with no RunAs setup it gives me only half the data with no errors. (We do use pass-through authentication) and when I try to put my credentials in the RunAs box - it fails to even run manually and gives error: Server user '%.*ls' is not a valid user in database '%.*ls'. This security is confusing me. – C-COOP Feb 22 '17 at 15:13
  • Has the database been recently moved from another server? Please see this article: https://support.microsoft.com/en-us/help/240872/how-to-resolve-permission-issues-when-you-move-a-database-between-servers-that-are-running-sql-server – OnoMrBill Feb 22 '17 at 15:21
  • Apart from the info in the article I mentioned before, you, or another administrator, should check the server login and database user that you plan to use to run the job on a regular basis. Quick question: are the 2 joined tables in the same database? We would usually use what we called a 'service account'; usually a SQL Login that has db_owner rights on the database. You could set one up as a test, to verify that it works, then fine-tune the security settings later. – OnoMrBill Feb 22 '17 at 15:27
  • hasn't recently moved, and it is 2 separate databases - actually 6 on the same instance. We don't do a lot of SQL work and was thinking about just leaving it using my account. – C-COOP Feb 22 '17 at 15:53
  • Since you're an administrator on that server, you should be able to create a SQL Login, unless your sql server is only set up to use Windows authentication, in which case you will have to use a Windows account. Either way, you create the Login under the Security folder of the main tree - right-click Logins - New Login - General page: for Windows login click Search to locate user account - For SQL Login, enter a meaningful name and password - Un-check 'User must change pw...' - Leave Default Database as 'master' - User Mapping page: tick db's; dbo as default schema – OnoMrBill Feb 22 '17 at 17:00
  • - Tick db_owner in bottom pane for each db Click OK button. This will add the new login to each of the selected databases by creating a user on each that is linked to the login you just added. – OnoMrBill Feb 22 '17 at 17:08
  • Tried another SQL account with sysadmin server role and still only get half the data and no errors with scheduler. Yet this job still works when I manually start it?? I am starting to miss Oracle lol. – C-COOP Feb 22 '17 at 18:43
  • As a side note - when I check the history, running job manually and using scheduler both are using the same admin user. – C-COOP Feb 22 '17 at 20:16
0

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

C-COOP
  • 123
  • 1
  • 3
  • 12