locked
SQL Agent job fails for user with sysadmin role RRS feed

  • Question

  • Hi I am trying to setup a sql agent job that does DBCHECK on user databases.

    The steps in the job are tsql and run  by user with sysadmin role but I keep getting 

    [SQLSTATE 01000] (Message 50000)  The user does not have permission to perform this action. [SQLSTATE 42000] (Error 297)

    SQL server 2012 R2

    Windows Server 2012

    Thursday, February 11, 2016 7:19 PM

Answers

  • hi,

    Check the owner of the agent job @ job properties and change it to sa. See if this works

    Hope this helps

    Thanks

    Bhanu

    • Marked as answer by bobghw Thursday, February 18, 2016 10:28 PM
    Thursday, February 11, 2016 7:56 PM

All replies

  • hi,

    Check the owner of the agent job @ job properties and change it to sa. See if this works

    Hope this helps

    Thanks

    Bhanu

    • Marked as answer by bobghw Thursday, February 18, 2016 10:28 PM
    Thursday, February 11, 2016 7:56 PM
  • What is service account running SQL Server agent. Does it has sysadmin permission ?

    IMO there are two things if owner of job is SA it will succeed but if it is not SA you need to make sure SQL agent account has sysadmins priv in SQL Server. To run checkdb you either need sysadmin priv or you need to be DB owner


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    Friday, February 12, 2016 9:47 AM
  • The Job owner essentially require SysAdmin to perform the job.

    Normally this is achieved by setting the owner of the Job to "sa".

    If the Job requires access to the operating system/network then you also need to ensure that the Service Account for SQL Server Agent (a) has SysAdmin access in SQL Server and (b) has the sufficient privileges on the Server/Network to complete operations on the Server/Network.


    Please click "Mark As Answer" if my post helped. Tony C.

    Friday, February 12, 2016 9:54 AM
  • No need to verify that Agent is sysadmin. If it isn't, the service won't start. So, we know that Agent is always sysadmin. Just as an FYI.

    If you don't believe me, remove the Agent Windows login from the sysadmin role, start Agent service and check then Agent errorlog. First I got some vague error messages. I then added the login for Agent as a user in msdb and made this db_owner. I now got this error message:

    SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role

    Regarding this thread problem: I wold double and tripple check that the job owner is a login who is indeed sysadmin and that no impersonation is defined for the job step. If this is a T-SQL job step, do a print of the output from ISSRVROLEMEMBER('sysadmin') function to get a verification that it is indeed executed as a sysadmin.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Proposed as answer by Charlie Liao Tuesday, February 16, 2016 1:22 AM
    Saturday, February 13, 2016 6:35 PM
  • Thanks setting owner as sa and removing the account in 'run as' in step has resolved.
    Thursday, February 18, 2016 10:29 PM