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.

   

   

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.