This issue is usually caused by the circumstances when the execution security context of the job is different from the security context when we manually run the script in SSMS. The commands in SQL Server Management Studio are executed as the current login. However by default every job step runs in the security context of SQL Server Agent Service account. If the SQL Server Agent Service account is different from the login account and if it does not have enough permission to execute the query, the job will fail.
To resolve this we can simply give the SQL Server Agent Service account permissions to execute the query, this is not the best way from a security perspective but it may be a temporary solution. As security perspective best practices, SQL Server Agent Service account is recommended to give it the least amount of privileges, and as a resultant we cannot create a SQL Server Agent proxy to execute the job because for T-SQL job step it will not use any SQL Server Agent proxy.
For a Transact-SQL job step, by default it runs under the security context of the job owner and if the owner of the job is a member of the sysadmin fixed server role, it runs under the SQL Server Agent service account.
Below are few methods to resolve this issue:
1. Changing the job owner to a login who has enough permission to execute the T-SQL script but the job owner should not be a sysadmin.
2. Specifying the “Run as User” option for the Transact-SQL job step in case you have several job steps and you do not want to change the job owner so as not to impact other job steps execution. You can go to the Advanced options of the job step, and input an user to the “Run as User” field. Ensure you are a sysadmin to edit this option.
3. Adding an EXECUTE AS statement ahead of your T-SQL script.
WikiDBA 