debugsqlMinor
Immediate e-mail alerting for error 229 (permission denied)
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
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.
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.