Running a TSQL type job (not SSIS) through Proxy

  • I have an job which has SQL queries and Stored procedures. Can this step be run through an proxy account. I created credential/proxy, but it didnt list down in "Run As". Can proxy be give only for SSIS, ActiveX, Replication etc and not for general TSQL queries and SP's?

  • balasach82 (8/24/2012)


    I have an job which has SQL queries and Stored procedures. Can this step be run through an proxy account. I created credential/proxy, but it didnt list down in "Run As". Can proxy be give only for SSIS, ActiveX, Replication etc and not for general TSQL queries and SP's?

    I could be wrong but I believe you have to create a login for the proxy for that to work.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • You cannot assign a proxy directly for TSQL steps.

    You have two options:

    1. Create an operating system proxy, and use sqlcmd to execute the TSQL.

    2. Create a Powershell proxy and use Powershell to execute your TSQL.

  • Since it's an SQL Server Agent job, I have to ask... why do you need a proxy to begin with?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I am trying to run a query in multiple server. In a SQL server A, I have created linked servers. When i run the query in QA, it runs,because my account has admin access in those servers. But when i try to schedule it, SQL agent account is different and in some servers, this account many not exist. Hence, I am looking at running the job under a proxy (like my account).

    I would be more than welcome to share more information if you need it.

  • balasach82 (8/26/2012)


    I am trying to run a query in multiple server. In a SQL server A, I have created linked servers. When i run the query in QA, it runs,because my account has admin access in those servers. But when i try to schedule it, SQL agent account is different and in some servers, this account many not exist. Hence, I am looking at running the job under a proxy (like my account).

    I would be more than welcome to share more information if you need it.

    It would be far better to set things up right so that SQL Server has privs on those other machines so you don't have to use a proxy, especially your login name.

    That, not withstanding, have you tried a simple EXECUTE AS?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Yes jeff, I tried to use EXECUTE AS, but it was a bit confusing, since I dont know whether I should give Agent account or my account in EXECUTE AS.

  • While I agree you shouldn't create a proxy for a standard user account, I see no problem with creating a proxy for a service account. I have done this on all my servers to automate the backup of the SSRS key.

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

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