Notify an Operator of Job Status

Go back to DP-300 Tutorials

In this we will learn the process to notify an Operator of Job Status. And, this tutorial also describes how to set notification options in SQL Server 2019 (15.x) by using SQL Server Management Studio, Transact-SQL, or SQL Server Management Objects.

Implement SQL Server Agent Security

SQL Server Agent lets the database administrator run each job step in a security context that has only the permissions required to perform that job step, which is determined by a SQL Server Agent proxy.
- However, to set the permissions for a particular job step, you create a proxy that has the required permissions and then assigns that proxy to the job step. For several work steps, a proxy can be defined. You also use the same proxy for task steps that require the same permissions.

Follow these guidelines to improve the security of your SQL Server Agent implementation:

Firstly, create dedicated user accounts specifically for proxies, and only use these proxy user accounts for running job steps.
Secondly, only grant the necessary permissions to proxy user accounts. Grant only those permissions actually necessary to run the job steps that are assigned to a given proxy account.
Thirdly, do not run the SQL Server Agent service under a Microsoft Windows account that is a member of the Windows Administrators group.
Fourthly, proxies are only as secure as the SQL Server credential store.
Next, if user write operations can write to the NT Event log, they can raise alerts via SQL Server Agent.
Do not specify the NT Admin account as a service account or proxy account.
After that, when a TSX enlists with an MSX, the MSX sysadmins gets total control over the TSX instance of SQL Server.
Lastly, ACE is an extension and cannot invoke itself. ACE is invoked by Chainer ScenarioEngine.exe – also known as Microsoft.SqlServer.Chainer.Setup.exe – or it can be invoked by another host process. However, ACE depends on the following configuration DLL’s owned by SSDP, because those API’s of DLL’s are called by ACE:
- SCO – Microsoft.SqlServer.Configuration.Sco.dll, including new SCO validations for virtual accounts
- Cluster – Microsoft.SqlServer.Configuration.Cluster.dll
- SFC – Microsoft.SqlServer.Configuration.SqlConfigBase.dll
- Extension – Microsoft.SqlServer.Configuration.ConfigExtension.dll

Using SQL Server Management Studio

To notify an operator of job status

Firstly, in Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
Secondly, expand SQL Server Agent, expand Jobs, right-click the job you want to edit, and select Properties.
Thirdly, in the Job Properties dialog box, select the Notifications page.
Next, if you want to notify an operator by e-mail, check E-mail, select an operator from the list, and then select one of the following:
- When the job succeeds to notify the operator when the job completes successfully.
- After that, when the job fails to notify the operator when the job completes unsuccessfully.
- When the job completes to notify the operator regardless of completion status.

However, if you want to notify an operator by pager, check Page, select an operator from the list, and then select one of the following:
- When the job succeeds to notify the operator when the job completes successfully.
- Next, when the job fails to notify the operator when the job completes unsuccessfully.
- When the job completes to notify the operator regardless of completion status.
Lastly, if you want to notify an operator by net send, check Net send, select an operator from the list, and then select one of the following:
- When the job succeeds to notify the operator when the job completes successfully.
- When the job fails to notify the operator when the job completes unsuccessfully.
- And, when the job completes to notify the operator regardless of completion status.

Using Transact-SQL