Original: A job can be run in SQL Server Management Studio but run with T-SQL fails
Problem:
You can run a job in SQL Server Management Studio but run with T-SQL and vice versa.
Analysis:This situation is mostly a context security issue at execution time. When executed in SSMs, the T-SQL statement is run under the current login name. However, the job is run in SQL Server Agent services (SQL Server Agent service account) with SQL Server Agent, and if the SQL Server Agent's accounts differ from those that execute T-SQL in SSMS The job will fail if the sample or permissions are different.
My practice is to use a high-privileged account to run SQL Server Agent, and have a separate account, while the password can not expire, or run for a period of time will not run. However, based on the "minimum security principle", excessive permissions are not generally recommended. Also, you cannot use the SQL Server Agent agent to execute a job because the T-SQL job step does not use any agents. For T-SQL job steps, the default is to run in the security context of the job owner.
Workaround: Method 1: Open the permissions of the job owner to large enough, but do not use the sysadmin. Method 2: Use the Run as user hint in a T-SQL job to execute the T-SQL job. This does not require changes to the original permissions. However, this process ensures that you have sufficient permissions to give the run as USER.
Method 3: This method is primarily scripted method 2, at the beginning of T-SQL Plus:
EXECUTE as user= ' xxxx '--the above statement gives the following script execution permissions to the XXXX login user. SELECT * from HumanResources.Department--after running the recovery permission: REVERT;
Finally, SQL Server Profiler can be used to monitor what account is executing the job.
You can run a job in SQL Server Management Studio but run with T-SQL to fail
