December 1, 2016 at 10:03 am
This job works fine from SSMS, but fails in agent job. I'm sysadmin and the
agent job user running job is as well. The job is just pulling data that matches from a table in one database and placing it in another table in another database.
USE master;
DECLARE @CurrentDate as datetime
SET @CurrentDate = GETDATE()
MERGE TEST.dbo._LOGINACTIVITY AS TARGET
USING TESTER.dbo.ACTIVITY as SOURCE
ON (TARGET.USERID = SOURCE.USERID)
WHEN MATCHED THEN
UPDATE SET TARGET.[LOGINDATE] = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
INSERT (USERID,[LOGINDATE])
VALUES(SOURCE.USERID, GETDATE());
The error code for failed job shows user is not able to access the database TEST
under the current security context. [SQLSTATE 08004] (Error 916).
Any ideas or suggestions would be greatly appreciated! I have been looking, but
can't seem to find the solution.
Thank you!
December 1, 2016 at 11:10 am
Hi, so when the t-sql is scheduled and exists in a Sql Server job, regardless of the owner of the job or other permissions, the Sql Server agent account needs the necessary permissions to the database and/or file locations the code is referring to. In short, try assigning the Sql Server agent account permissions in Sql Server to the database and retry the job.
December 1, 2016 at 11:49 am
I actually got it to work, but what I did was remove the user from the "Run as user" in steps property on advanced tab. Thanks!
December 2, 2016 at 6:16 am
butcherking13 (12/1/2016)
I actually got it to work, but what I did was remove the user from the "Run as user" in steps property on advanced tab. Thanks!
Thanks for sharing the solution!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply