Post reply

SQL Agent - Run As Help

  • February 11, 2009 at 9:16 am

    #128720

    I am building a system to help monitor all of our SQL Servers, databases, DB growth, etc. One thing I have done is to build a series of procedures on one server that I want to run as a SQL Agent job and each of these procedures will hit many linked servers and pull back the information needed.

    My problem is that the job is running as the SQL Server Agent Service Account, as I know it should, normally, but I want to run it with an account that has sysadmin setup on all servers. Once I get it working, I will setup a special network account specifically for this purpose. In the meantime, I want it to execute as my own special administrative login that I use to administer my servers. This account is a sysadmin on ALL servers so it will have the permissions I need. No matter what I do, though, the job continues to run as the service account.

    I have looked at Credentials and Proxies but they won't work for me here because I am using the T-SQL job step type and that type of step does not allow proxies.

    Any ideas?

    ----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

  • February 11, 2009 at 9:27 am

    #942270

    You could create an SSIS package, using SQL Tasks to execute the SPs.

    Then you could use a proxy to execute the SSIS Package job step type (which does accept proxies)

    ------------------------------------------------------------------------
    Bite-sized fiction (with added teeth) [/url]

  • February 11, 2009 at 9:29 am

    #942274

    Thanks, I'll look into that. I also had another idea of executing on procedure and using the EXECUTE AS function to change the context.

    Either of these should work.

    ----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

  • February 11, 2009 at 9:29 am

    #942275

    A Grandmother...Eggs question here sorry, but have you tried using the "Run as User" box at the bottom of the "Advanced" tab of the Job Step Properties?

    Also, if your steps consist of invoking SPs, then how about altering the SPs to use "Execute As" discussed here:-

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

  • February 11, 2009 at 9:39 am

    #942287

    Andy Hogg (2/11/2009)

    A Grandmother...Eggs question here sorry, but have you tried using the "Run as User" box at the bottom of the "Advanced" tab of the Job Step Properties?

    Ah.

    Ignore me, hawg - I have some eggs to see to, clearly, despite being somewhat lacking in the granchildren department 😀

    ------------------------------------------------------------------------
    Bite-sized fiction (with added teeth) [/url]

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

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