Force T-SQL Job Step to Run Under Specific SQL Account

Question

Force T-SQL Job Step to Run Under Specific SQL Account

Jared

SSC Guru

Points: 61793
More actions
September 25, 2013 at 7:17 am

#299143

I have created a SQL user to run certain dbcc commands for us (DBCC CHECKTABLE in this case). This user is governed by resource governor for this reason. I made the user a sysadmin and also made it the owner of the job. What I found later, was that if the owner of the job is a sysadmin, it will execute in the context of the agent account. If the owner is not sysadmin, it will execute under the context of the owner.
Does anyone have any ideas as to how I can get this job to execcute as this new SQL login instead of the agent, but still have it with server role of sysadmin?
Jared
CE - Microsoft

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

You can specifiy the user the job step should run as, http://technet.microsoft.com/en-us/library/ms187358(v=sql.105).aspx

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jared SSC Guru Points: 61793 More actions · Answer 2

Jack Corbett (9/25/2013)
You can specifiy the user the job step should run as, http://technet.microsoft.com/en-us/library/ms187358(v=sql.105).aspx

I thought that this can only be a proxy, which has to be a Windows account.

Jared
CE - Microsoft

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Nope, T-SQL job steps can't use proxies but they can use RUN AS which, if I understand correctly, uses EXECUTE AS in the background, so the user has to exist in the DB and the owner of the job has to be a member of the sysadmin server role.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jared SSC Guru Points: 61793 More actions · Answer 4

Ha! Thanks! I missed that parameter.

Things to note:

1. The user has to be explicitly added as a user to master (or whatever database the step is executing under), even if it is a sysadmin.

2. If you do this through the GUI, this is not under the RUNAS on the main tab, it is in run as on the Advanced tab.

Jared
CE - Microsoft

Jared SSC Guru Points: 61793 More actions · Answer 5

So... I set this up and now I keep getting an error on the step:

Executed as user: new_user. The server principal "new_user"

is not able to access the database "userdatabase1" under the current security context.

[SQLSTATE 08004] (Error 916). NOTE: The step was retried the requested number of times (3) without succeeding.

The step failed.

NOTE: This user is sysadmin. I have also tried adding the user as db_owner to all user databases and still get this error... Any thoughts?

Jared
CE - Microsoft

j.a.c SSCrazy Points: 2283 More actions · Answer 6

I think the target db needs to have the TRUSTWORTHY option set high. Not the best solution but I was able to get DBCC CHECKTABLE working in a SQL Agent job with another user executing the job step.

Andy

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 7

SQLKnowItAll (9/26/2013)
So... I set this up and now I keep getting an error on the step:
Executed as user: new_user. The server principal "new_user"
is not able to access the database "userdatabase1" under the current security context.
[SQLSTATE 08004] (Error 916). NOTE: The step was retried the requested number of times (3) without succeeding.
The step failed.
NOTE: This user is sysadmin. I have also tried adding the user as db_owner to all user databases and still get this error... Any thoughts?

I had to give user explicit public role to master database even if the user is sysadmin, after this it worked.