Script won't run when placed into a Job step

Question

Script won't run when placed into a Job step

Viewing 4 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Brandie Tarvin SSC Guru Points: 173108 More actions · Answer 1

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.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

RML51 SSCarpal Tunnel Points: 4410 More actions · Answer 2

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.

Brandie Tarvin SSC Guru Points: 173108 More actions · Answer 3

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.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

RML51 SSCarpal Tunnel Points: 4410 More actions · Answer 4

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.