Post reply

How to run TSQL Job Step under different account

  • July 22, 2011 at 9:13 am

    #390036

    The job owner account has no permissions except for what is stated below.

    I need to run an Exec Stored Procedure command as a TSQL job step. How do I run that job step as a different account?

    I gave the job owner account (jobOwnerAcct) impersonate rights to another account ('Domain\login')

    Here is the job step TSQ command:

    EXECUTE AS LOGIN = 'Domain\login'

    go

    EXEC [dbo].[mySproc]

    Here is the error

    Message

    'EXECUTE AS LOGIN' failed for the requested login 'jobOwnerAcct'. The step failed.

    The job owner has impersonate on Domain\login and Domain\login has the rights to execute the procedure and has access to all data. I can't think of any other way to do this other than put the execute as in the stored procedure and give the owner account access to execute the procedure.

    BUT that goes against giving owner accounts permissions. The idea was that job owners have 0 rights and everything runs under proxies. Looking for a suggestion here.

    Thanks

  • July 22, 2011 at 10:59 am

    #1357193

    so on the advanced tab there is an execute as option that I had missed. Well that produces the same error as putting the Execute AS in the sql statement.

  • July 22, 2011 at 11:11 am

    #1357198

    Are there any errors in the error log that give a little more detail? Have you tried using execute as user instead? I doubt it'll work but it's something easy to try. I also found this blog post with some other things to try:

    https://danieladeniji.wordpress.com/2011/07/19/microsoft-sqlserver-database-job-failed-execute-as-login-failed-for-the-requested-login/

  • July 22, 2011 at 12:54 pm

    #1357255

    no errors tried all options, not going to set trustworthy on as that blog suggests. did everything else nothing.

    So i just put the stored procedure call in my SSIS package, that works fine.

    Although I'm sure I could setup impersonation from job owner to another account and use Execute AS within the stored procedure then give job owner execute on the sproc. that is not a good option either since the job owners are supposed to be void of any rights in our environment.

    Apparently when running TSQL your only option is to run it as the job owner, which i'm sure is due to potential security risks.

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

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