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.

On the User’s machine:

1. Manually create a path in Windows Explorer on the user's workstation, example: C:\SQLBATCH
2. 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
3. Next, create a windows batch file on the user's workstation as follows:
   1. Create a New Text document and rename the extension 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:\SQLBATCH

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

ECHO Job execution completed

pause

CLS

EXIT

4. 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.

Working example:

Create a folder named SQLBATCH on the user’s machine. Then create a file named SQLJOBSTART.bat

Enter the following, replacing names where necessary. Then save the file :   

ECHO Executing job
ECHO.
sqlcmd -S "VM-SQL1" -Q "exec msdb.dbo.sp_start_job 'Sage 500 ERP sage500_app QtyOn'"
ECHO Job execution completed
pause
CLS
EXIT

The above shows an example of a batch file that will execute the Sage 500 QtyOn job on machine named  VM-SQL1. For details about the switches used by the sqlcmd, type sqlcmd /? from a command window.  The results from the execution run appear below.

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.