December 2, 2009 at 6:47 am
I have a script, from a vendor, that I want to run in a job step, so I can schedule it to run on a weekend night. The script performs an Insert. The script runs successfully, and performs the Inserts, when it's run from Query Analyzer. The same script runs, but does not successfully perform the Inserts if it's placed in a SQL Server Agent Job. To be clear, the script appears to successfully execute in the job step - it runs for several minutes, just as it does in Query Analyzer - but it doesn't perform the Inserts.
The job owner has sysadmin rights and is used to execute other jobs that perform updates, so it's not a permissions issue.
Has anyone seen this behavior before?
SS2005 Enterprise 64bit - SP3 w/CU1
December 2, 2009 at 8:01 am
I have when it's a permissions or ownership chain issue. Without seeing the script, I can't be for sure exactly what the issue is.
You say Query Analyzer. Do you mean Management Studio? If so, have you tried using "Execute AS" in SSMS to run the script? That might give you more detail than just running the script as yourself.
December 2, 2009 at 8:10 am
Yes - from Management Studio (QA = bad habit left over from SQL2K). The inserts work fine when run from MS, but won't work when run in a job step. The job is executed with a sysadmin user.
December 2, 2009 at 8:13 am
Okay, so you've tested the scripts using the Execute AS feature...
What does the script affect? Just tables in a database or does it access other things outside the database?
Have you verified that you have no hidden DENY perms on anything? That can also screw stuff up.
Is the user a SQL Login or domain account? If domain, is it from the same domain as the server was set up / installed in?
Sometimes even a sysadmin user needs to be granted specific permissions (like execute on procs) for things to work via job steps.
December 2, 2009 at 8:51 am
Brandie Tarvin (12/2/2009)
Okay, so you've tested the scripts using the Execute AS feature...
Not yet - testing involves other users to verify the results. I'll have to see if I can coordinate this.
What does the script affect? Just tables in a database or does it access other things outside the database?
It does several JOINs to build a work table in a separate workspace database and then INSERTS that data into the "production" database. It doesn't pull in any flatfiles or non-database tables.
Have you verified that you have no hidden DENY perms on anything? That can also screw stuff up.
There are no DENY permissions set.
Is the user a SQL Login or domain account? If domain, is it from the same domain as the server was set up / installed in?
It's a domain account and it's in the same domain as the server. The same domain account is used in several other existing jobs to perform inserts and updates.
Thanks
Sometimes even a sysadmin user needs to be granted specific permissions (like execute on procs) for things to work via job steps.
There are no procs involved.
Being as this is a vendor written script, I'm not at liberty to post it. I guess I'm asking if anyone has every seen this behavior before and what might cause it. The vendor is looking into this, and I'll post a reply when they give me a solution.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply