FireDAC permite "en teoría" capturar esos eventos con el componente
TFDEventAlerter.
Revisa la ayuda de la docWiki al respecto
Entre los ejemplos que vienen con Delphi hay uno que permite ver cómo funciona para SQLServer en concreto.
Revisa este ejemplo:
"c:\Users\Public\Documents\Embarcadero\Studio\19.0\Samples\Object Pascal\Database\FireDAC\Samples\DBMS Specific\MSSQL\QueryNotify"
Por aquí comentan que también se puede hacer utilizando WMI. SQL Server como proveedor WMI registra sus clases, y puedes acceder a ellas.
De todas formas, parece que la primera opción con FireDAC debería ser más sencilla.