locked
No proxies for T-SQL RRS feed

  • Question

  • Hi, 

    I want to run a SQL Agent step as a different user than the one used by the Agent service.  I thought that I would create a new credential and then have a proxy use it, however there are none for just plain old SQL.  This is a SQL 2014 Enterprise instance. 

    The reason for this is that I the Windows login that is used by the Agent service is not defined as a login in the database, I don;t want to therefore add it in as a user with restrictive rights it case it impacts on other jobs.  

    What is the easiest way of have a single job step run under a different Login? 

    Monday, July 20, 2015 3:00 PM

Answers

  • The job isn't executed in some particular security context. The job*step* is.

    For TSQL jobsteps, you just use the advanced tab and use the "Run as User" textbox to specify the *user* (not login) it should be executed asl.


    Tibor Karaszi, SQL Server MVP | web | blog

    • Proposed as answer by Charlie Liao Tuesday, August 4, 2015 1:47 AM
    • Marked as answer by Charlie Liao Wednesday, August 5, 2015 2:05 AM
    Monday, July 20, 2015 7:50 PM
  • If you are running as the SQL Agent, it should have sysadmin access by default.  If not, you can try to use the EXEUTE AS feature in your T-SQL statement.

    https://msdn.microsoft.com/en-us/library/ms188354.aspx

    • Proposed as answer by Charlie Liao Tuesday, August 4, 2015 1:47 AM
    • Marked as answer by Charlie Liao Wednesday, August 5, 2015 2:05 AM
    Monday, July 20, 2015 4:03 PM

All replies

  • however there are none for just plain old SQL.  

    Hello,

    Indeed we don't have Proxy for plain T-SQL steps, because they are executed in the context of the job owner; so if you create the Job then in context of your credentials.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, July 20, 2015 3:12 PM
  • Hi, 

    Changing the job owner makes no difference.  Without a credentail/proxy ow can I make the job run under a different login?  Do I have to start SSMS as that user? 

    Thanks. 

    Monday, July 20, 2015 3:44 PM
  • If you are running as the SQL Agent, it should have sysadmin access by default.  If not, you can try to use the EXEUTE AS feature in your T-SQL statement.

    https://msdn.microsoft.com/en-us/library/ms188354.aspx

    • Proposed as answer by Charlie Liao Tuesday, August 4, 2015 1:47 AM
    • Marked as answer by Charlie Liao Wednesday, August 5, 2015 2:05 AM
    Monday, July 20, 2015 4:03 PM
  • The job isn't executed in some particular security context. The job*step* is.

    For TSQL jobsteps, you just use the advanced tab and use the "Run as User" textbox to specify the *user* (not login) it should be executed asl.


    Tibor Karaszi, SQL Server MVP | web | blog

    • Proposed as answer by Charlie Liao Tuesday, August 4, 2015 1:47 AM
    • Marked as answer by Charlie Liao Wednesday, August 5, 2015 2:05 AM
    Monday, July 20, 2015 7:50 PM
  • Good job Tibor.  I didn't know that was even an option under the advanced tab.
    Monday, July 20, 2015 9:14 PM
  • It is well hidden. :-) Note that it will "simulate" the user, not the login. If you need to simulate a different login instead of the user, you can't use the GUI. You would need to add the EXECUTE AS LOGIN = xyz in the beginning of your TSQL code. But I suggest using the option in the GUI, assuming that the user context is enough - it is (a bit) more explicit than having the EXECUTE AS command in the code...

    Tibor Karaszi, SQL Server MVP | web | blog

    • Proposed as answer by Charlie Liao Tuesday, August 4, 2015 1:47 AM
    Monday, July 20, 2015 9:56 PM
  • Hi Olaf 

    "....so if you create the Job then in context of your credentials...."

    There seems to be something missing from this sentence...


    Cheers Greg

    Tuesday, March 22, 2016 11:13 PM