locked
Permissions for SQL LOGIN a/c to run agent job. RRS feed

  • Question

  • Hey Guys,

    I am receiving the following error running my dtsx package from sql 2005 SQL Agent Jobs.

    Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy accoutnt.

    For more details:

    1) running job with sql login, only have SQLAgentoperatorrole

    2) agent service is being run by one of the system accounts.

    3) As error showing: i am not using any proxy account.

    Job is only quering a database on the same server(not a multiserver job) and compare whether things are due or not.

    and in the end it also send mails to the users. Everything is written in VB script and package was well and truly running in the SSIS.

    When i run job with sysadmin permissions to sql login it runs fine. Please guide me what permissions i have to give to run my job.

    Thanks in advance everyone.

    Monday, January 31, 2011 7:02 AM

Answers

All replies

  • http://www.sql-server-performance.com/articles/dba/sql_server_job_proxy_account_p1.aspx
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, January 31, 2011 7:23 AM
  • You will have to create a proxy account to make that job run.

    http://msdn.microsoft.com/en-us/library/ms190698(v=SQL.90).aspx

    http://support.microsoft.com/kb/912911

    Please see the above documents for help.

    Regards

    Ashwin

    Monday, January 31, 2011 7:25 AM
  • Hi Uri and Ashwin,

    thanks for replying. Could you please guide me using without proxy account because i have already done it with proxy account.

    reason why i dont want to use proxy account is because they are windows login and they tend to expire their passwords after certain time.

    i can use system accounts but i was looking for a way to run my jobs with only sql logins.

    Appretiate your help.

    Monday, January 31, 2011 7:55 AM
  • Somy13,

    What account are you using for SQL Agent service? Is it a domain account or a NT AUTHORITY\System or NT AUTHORITY\NetworkService?

    http://msdn.microsoft.com/en-us/library/ms191543%28v=SQL.90%29.aspx

     

    Depends on how your create your credential for your proxy account, you should be able to avoid using domain account if you choose to use NT AUTHORITY... but you might have other limits.


    Sevengiants.com
    Monday, January 31, 2011 9:43 AM
  • Only job owners can run run their jobs

    BOL says

     

    By default, members of the sysadmin fixed server role can execute this stored procedure. Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb database:

    • SQLAgentUserRole
    • SQLAgentReaderRole
    • SQLAgentOperatorRole

    For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles.

    Members of SQLAgentUserRole and SQLAgentReaderRole can only start jobs that they own. Members of SQLAgentOperatorRole can start all local jobs including those that are owned by other users. Members of sysadmin can start all local and multiserver jobs.


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, January 31, 2011 10:14 AM
  • Hi Seven,

    Its running under one of the networkservice account which is a newly created account by my Network administrator.

    Tuesday, February 1, 2011 8:37 AM
  • Hi Uri,

    So it means i cant run my job with SQL Login without giving sysadmin permissions or without using Proxy account.

    So far, from google and with Uri and Seven's help i think i better have to get a new service account from my administrator to run this job and i have to use this account as a proxy account to run the job. Because proxy account will also sort out the password expiration problem.

    Am i heading in right direction guys?

    Thnx heaps anyway.

     

    Tuesday, February 1, 2011 8:46 AM
  • >>>Am i heading in right direction guys?

    Yep, generally , use Proxy account if you want non sysadmin running jobs


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, February 1, 2011 9:37 AM
  • >>>Am i heading in right direction guys?

    Yep, generally , use Proxy account if you want non sysadmin running jobs


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, February 1, 2011 9:37 AM