locked
SQL 2005 Agent Job failed with State: 8 RRS feed

  • Question

  • Hello,

    I'm using SQL 2005 and try to create a SQL Agent Job.

    I have a stored procedure named SP_Test with only one select statement on the local database A. The stored procedure is created by user: pesa and pesa is the db_owner of database A.
    Then I created a Job in SSMS using pesa account and set the Job owner as pesa and Run as user: pesa.

    When I ran the Job inside SSMS with pesa account, I got the following error:
    2010-02-24 11:30:01.43 Logon       Error: 18456, Severity: 14, State: 8.
    2010-02-24 11:30:01.43 Logon       Login failed for user 'pesa'. [CLIENT: <local machine>]

    I understand that State: 8 means 'Password mismatch' . But I didn't enter any password when I ran the Job.

    By the way, pesa is also a system admin account on the SQL 2005 server.

    Any help is appreciated.

    Regards,

    Shirley Xie

    Wednesday, February 24, 2010 5:00 PM

All replies

  • Can you check the job history and see what error is shown ?
    Thanks, Leks
    Wednesday, February 24, 2010 5:56 PM
  • Hi Lekss,

    Thanks for your reply.

    This message is shown at View Job History:
    Unable to perform a SETUSER to the requested username 'pesa' because the username is invalid for database A. The step failed.

    I went to Database A\Security\Users\pesa --> General --> Databse Role Membership: db_owner

    Any other steps I need to check?


    Thanks,

    Shirley
    Wednesday, February 24, 2010 6:21 PM
  • Hello,

    I managed to make the Job calling stored procedure: SP_Test work by removing the user at Run As User and leaving it blank, and add the user who runs the Job has a mapping at Local server login to remote server login mapping for the Linked server. By the way, the stored procedure needs to access Linked server.

    Thanks,

    Shirley
    Thursday, February 25, 2010 2:35 PM
  • check if db user pesa is correctly mapped with the login . Also check the proxy account that you may be using in run as option ...
    Thursday, October 21, 2010 3:30 AM