RKL eSolutionsBlogs

Run a SQL Agent Job from a Windows BAT File

There is often a need for end-users to run SQL Jobs on an ad-hoc basis. This creates a security issue because we don't want to install SQL Server Management Studio on the end-user workstation and we don't want to give out Remote Desktop access to the SQL Server or grant additional security within SQL.

Here is a quick process that solves these issues and allows an end-user to run a SQL Server Agent Job from a Windows BAT file on their workstation:

  1. Create the job in SQL Server so it exists and runs successfully from the server.
  2. Create a new SQL Server login for the end-user's windows authenticated account within the SQL Server security node
  3. In the User Mapping page, grant the user SQLAgentOperatorRole for MSDB. This will allow the user to start a SQL Agent job without giving them unneeded permissions in SQL.

    Run a SQL Agent Job from a Windows BAT File

  4. Manually create this path in Windows Explorer on the user's workstation: C:Program FilesMicrosoft SQL Server100ToolsBinn
  5. Copy OSQL.exe file from a machine with an installation of SQL Tools (should be in the same or similar directory) to the user's workstation
  6. Next, create a windows batch file on the user's workstation as follows:
    1. Create a New Text document and rename the extention from TXT to BAT on the user's desktop
    2. Right-click the new BAT document and choose Edit
    3. Enter the following text in the new BAT document replacing [SQL SERVER NAME] and [SQL JOB NAME] with your actual names

      ECHO Executing job

      ECHO.

      CD C:Program FilesMicrosoft SQL Server100ToolsBinn

      osql -S "[SQL SERVER NAME]" -E -Q"exec msdb.dbo.sp_start_job '[SQL JOB NAME]'"

       

      ECHO Job execution completed

      pause

      CLS

      EXIT

  7. Save the BAT file and double-click it to execute it

NOTE: the BAT file will return a successful message indicating the job was started. It doesn't tell you if the job had errors. An administrator on SQL still needs to monitor the jobs for errors.

 Want to learn more about how having an experienced Sage partner can help your business?

Get In Touch >>

Tagged With: SQL Server

Questions? Need Support?
Get in Touch