You can run a job in SQL Server Management Studio but run with T-SQL to fail

Source: Internet
Author: User
Tags management studio sql server management sql server management studio

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.