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:
- Create the job in SQL Server so it exists and runs successfully from the server.
- Create a new SQL Server login for the end-user's windows authenticated account within the SQL Server security node
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.
- Manually create this path in Windows Explorer on the user's workstation: C:Program FilesMicrosoft SQL Server100ToolsBinn
- 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
Next, create a windows batch file on the user's workstation as follows:
- Create a New Text document and rename the extention from TXT to BAT on the user's desktop
- Right-click the new BAT document and choose Edit
Enter the following text in the new BAT document replacing [SQL SERVER NAME] and [SQL JOB NAME] with your actual names
ECHO Executing job
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
- 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?