T-SQL Statement Task not running when scheduled

Question

T-SQL Statement Task not running when scheduled

Viewing 9 posts - 16 through 23 (of 23 total)

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

Luv SQL SSCommitted Points: 1544 More actions · Answer 1

I tried the Credential then Proxy but there was no option on the Proxy to run transact-sql type jobs. Plus I wanted to use a SQL login and didn't work under the Credential part.

Sue_H SSC Guru Points: 90860 More actions · Answer 2

Luv SQL - Wednesday, November 15, 2017 2:21 PM
I tried the Credential then Proxy but there was no option on the Proxy to run transact-sql type jobs. Plus I wanted to use a SQL login and didn't work under the Credential part.

But we still have no idea at all what you are even trying to do other than an insert somewhere. And we know you want to use a SQL login. Without the seeing the SQL statement, it's all just wild guesses. My guess is this could be resolved pretty quickly if we could see what exactly you are trying to do.

Sue

Phil Parkin SSC Guru Points: 246979 More actions · Answer 3

Luv SQL - Wednesday, November 15, 2017 2:21 PM
I tried the Credential then Proxy but there was no option on the Proxy to run transact-sql type jobs. Plus I wanted to use a SQL login and didn't work under the Credential part.

All of your problems would likely be easy to resolve if you could change the SQL Agent service to run as a Windows domain service account.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Sue_H SSC Guru Points: 90860 More actions · Answer 4

Luv SQL - Wednesday, November 15, 2017 1:52 PM
I know super frustrating. If I change the owner of the job, is there a reason why it won't use this account to run the actual transact-sql statement within the steps? I've changed the owner of the job to be a specific sql login, but it still shows "executed as NT AUTHORITY\NETWORK SERVICE" ie the SQL Agent account. Since I can't add this account to the permissions of a sql view, I can't modify the permissions. We don't have any Windows Authenticated users to use either.

If that is your service account, it is a sysadmin.
If it wasn't you could still add permissions using:
GRANT INSERT ON YourSchema.YourView TO [NT AUTHORITY\NETWORK SERVICE]

Sue

Sue_H SSC Guru Points: 90860 More actions · Answer 5

Luv SQL - Wednesday, November 15, 2017 2:21 PM
I tried the Credential then Proxy but there was no option on the Proxy to run transact-sql type jobs. Plus I wanted to use a SQL login and didn't work under the Credential part.

There is no proxies for the T-SQL subsystem. If you wanted to execute a stored procedure or query using a proxy, you would need to Powershell or Operating Sytem/CmdExec subsystems. And before you say that doesn't work, it's just an Operating System job step using SQLCMD.

Sue

Sue_H SSC Guru Points: 90860 More actions · Answer 6

Luv SQL - Wednesday, November 15, 2017 1:52 PM
I've changed the owner of the job to be a specific sql login, but it still shows "executed as NT AUTHORITY\NETWORK SERVICE" ie the SQL Agent account.

T-SQL jobs that are owned by sysadmins will execute under the security context of the Agent service account.
T-SQL jobs that are owned by non-sysadmins will start under the security context of the Agent service account and then does an EXECUTE AS LOGIN =<jobowner> to impersonate the owner and then executes the t-sql.

Sue

Phil Parkin SSC Guru Points: 246979 More actions · Answer 7

Sue_H - Wednesday, November 15, 2017 3:02 PM
T-SQL jobs that are owned by sysadmins will execute under the security context of the Agent service account.
T-SQL jobs that are owned by non-sysadmins will start under the security context of the Agent service account and then does an EXECUTE AS LOGIN =<jobowner> to impersonate the owner and then executes the t-sql.
Sue

Thanks, Sue, I did not know that.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Steve Jones - SSC Editor SSC Guru Points: 736458 More actions · Answer 8

Late, but if you are running this in SSIS, have you enabled logging for the package and check that? The Job logs aren't great, but if you look at SSMS and the base job history, you will miss details as well.

I think Sue has something on the right track here. You need to be sure permissions work. I'd start by simplying to a simple T-SQL statement. Forget your code. Create a small table and do an insert or update in this table. Debug the job process separate from the code issues.

Luv SQL SSCommitted Points: 1544 More actions · Answer 9

Luv SQL

SSCommitted

Points: 1544

November 16, 2017 at 8:09 am

#1968371

Good to know! Thanks Sue.