Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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'
share|improve this answer
    
I used to do this back in SQL Server 2000 when I was full on DBA. Interesting what you catch folk trying... –  gbn Jan 27 '12 at 14:47
    
Yeah, I'm sure I'll find a snoop here or there using Access or Management Studio or something. Mostly I want to make sure accounts have enough permission. A number of users/roles have way too many privileges, and I'm trying to dial it back without breaking our ERP/sales software. Combined with the user errors trace I have running on the server, this should give me enough forensic detail (though I'd love to figure out how to get the user name to appear in the alert email). –  db2 Jan 27 '12 at 15:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.