sp_MonitorPermissionErrors

This is a free tool from DataPaws to monitor and alert on permission errors and login failures in SQL Server using Extended Events. It’s designed to capture, filter, aggregate, and alert on security-related errors with flexible configuration and minimal overhead.

Requirements

• SQL Server 2016+
• Database Mail (For alerts)

What does sp_MonitorPermissionErrors do?

• Monitors for permission errors and login failures
• Aggregates errors for summarized email alerts
• Supports flexible INCLUDE / EXCLUDE filtering logic
• Tracks processing state using persistent file offsets for incremental reads for optimal performance

Alerting

If @EmailAlerts = 1, this sends table-formatted emails summarizing detected permission errors.
If no recipients are provided, it uses the email assigned to the default SQL Agent Operator or a specific operator defined by @DBMailOperatorName.

Details Included:

• Error message
• Login
• Database
• Application
• Hostname
• Error count
• Latest occurrence time

Filtering

Filters are fully customizable and stored in the dbo.MonitorPermissionErrorsFilters table by default.

Filters are evaluated in the following order:

1. INCLUDE filters (if any exist)
   • Only matching events are allowed through
   • Multiple INCLUDE filters use OR logic
2. EXCLUDE filters
   • Remove unwanted events from remaining results

Supported filter columns:

• error_number
• severity
• login
• hostname
• database_name
• application
• message_pattern (LIKE syntax supported)
• threshold (minimum occurrences required before alerting)

Example Filters

Exclude login failures for a specific user

INSERT INTO dbo.MonitorPermissionErrorsFilters (filter_name, error_number, login, filter_action)
VALUES ('Exclude user1 login failures', 18456, 'user1', 'EXCLUDE');

Exclude a noisy application

INSERT INTO dbo.MonitorPermissionErrorsFilters (filter_name, application, filter_action)
VALUES ('Ignore MyApp noise', 'MyApp', 'EXCLUDE');

Only alert when errors occur 5+ times

INSERT INTO dbo.MonitorPermissionErrorsFilters (filter_name, threshold, filter_action)
VALUES ('Threshold filter', 5, 'INCLUDE');

Only monitor a specific login

INSERT INTO dbo.MonitorPermissionErrorsFilters (filter_name, login, filter_action)
VALUES ('Monitor user1 only', 'user1', 'INCLUDE');

Example Usage

The tool is designed to run as part of a scheduled SQL Agent Job, typically every 5–15 minutes.

Basic Usage

EXEC dbo.sp_MonitorPermissionErrors; 

Custom XE File Location + Email Recipients

EXEC dbo.sp_MonitorPermissionErrors 
    @XEFileLocation = 'D:\SQLExtendedEvents\',
    @EmailRecipients = 'dba@company.com;alerts@company.com';

Disable Email Alerts

EXEC dbo.sp_MonitorPermissionErrors 
    @EmailAlerts = 0;

Force XE Session Recreation

EXEC dbo.sp_MonitorPermissionErrors 
    @RecreateXESession = 1;

Use Custom Tables

EXEC dbo.sp_MonitorPermissionErrors
    @XEOffsetsTable = 'dbo.CustomXEOffsets',
    @FiltersTable = 'dbo.CustomFilters';

Debug Mode

EXEC dbo.sp_MonitorPermissionErrors 
    @Debug = 1;

Recommended Setup

• Create a SQL Agent Job that runs the stored procedure every 5–15 minutes
• Configure Database Mail and Operator
• Add filters to suppress known / expected noise
• Monitor initial alerts and refine filters over time