HiveBrain v1.2.0
Get Started
← Back to all entries
debugsqlMinor

Immediate e-mail alerting for error 229 (permission denied)

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
errorpermission229deniedmailforimmediatealerting

Problem

For a few reasons, I'd like to have all permission denied errors (number 229) emailed to me. Checking master.sys.messages shows that is_event_logged for this error is 0. Thus that rules out using SQL Server Agent error alerts, which rely on the SQL Server error log (I've tested this - I get no alert).

I figured I'd peek in mssqlsystemresource and look at the view definition for sys.messages, thinking maybe I could update is_event_logged for the message in question. But this view gets the system error message from OpenRowset(TABLE SYSERRORS), so that's a no-go.

Is there a reasonably simple way I can get all 229 errors emailed to me immediately (or within maybe 30 seconds) without harming server performance? A 60-second cool-down between emails would probably be a good idea too.

Solution

Oh, damn it, not 30 seconds after I post this, I stumble across sp_altermessage (naturally, after spending a significant amount of time trying to figure out if I could do this with extended events). So if anybody else is wondering how to do this:

EXEC sp_altermessage 229, 'WITH_LOG', 'true'

Code Snippets

EXEC sp_altermessage 229, 'WITH_LOG', 'true'

Context

StackExchange Database Administrators Q#11772, answer score: 5

Revisions (0)

No revisions yet.