Many "The user does not have permission to use the KILL statement." messages are printed in the SQL Server Error Log (4381725)

In some environments, the SQL Server agent’s *session cleaner* feature is unable to kill (“clean up”) its own idle or orphan sessions in Microsoft SQL Server. This can leave agent-related sessions open on the server, even after they are closed on the client side and many "The user does not have permission to use the KILL statement." messages are printed in the SQL Server Error Log.

By default, the SQL Server agent grant permissions script does not allow the agent to kill SQL Server sessions. The required permission would be membership in a sysadmin or processadmin server lor or the server level permission ALTER ANY CONNECTION that is available in SQL Server.

Without one of these, attempts to execute KILL {spid} fails with a permissions error. The rest of the agent's features to monitor still functions, but idle agent sessions could accumulate and not be removed by Foglight.

If permitted by your organization, replace FoglightUser with your login in the script below and then grant the ALTER ANY CONNECTION permission to the SQL Server login to enable session clearing.

USE [master];
GO
GRANT ALTER ANY CONNECTION TO [FoglightUser];
GO

Notes:

• This permission enables the agent to kill *any* session on the SQL Server, not just its own.
• If your organization policy doesn't allow this permission, the session cleaning feature will not function, but routine monitoring is unaffected.
• Granting 'ALTER ANY CONNECTION` is a privileged action. Only grant it to trusted service accounts as needed for agent cleanup.

STATUS

Defect ID FOG-12812 has been logged to add this permission to the Foglight SQL Server agent documentation.