Stored procedure runs fine when executed manually but fails in scheduled job
The probable reason I can think is when u execute manually it might be because you are executing with login with privileges or access to execute that proc.
However sql agent job might be using the account assigned to run sql agent services.
Hence you see the error for login failed.
Probably check the error log when failure happens to get more details on that login failure message