December 12, 2006 at 3:39 pm
Sorry if this gets posted twice - I submitted this and it went back to the "create new topic" page. Don't think it went through the first time. glad I copy & pasted it out before submitting. Anyway, on to my post:
Hello all.... I'm working on upgrading our SQL 2000 servers to SQL 2005. Currently have a single SQL 2005 box up & running, and am testing apps on it.
One thing that I have set up for all my SQL 2000 machines is an alert that gets raised and then emailed to me with every permission denied (229) error. Pretty helpful for debugging, monitoring, etc. A prerequisite for getting this alert to work is setting that error #229 be logged by doing:
sp_altermessage 229, 'WITH_LOG', 'true'
Tried this in 2005, and find out that you can no longer update system error messages.
Msg 15178, Level 16, State 1, Procedure sp_altermessage, Line 20
Cannot drop or alter a message with an ID less than 50,000.
Ugh. Even tried this:
UPDATE sysmessages SET dlevel = (dlevel | 0x80) WHERE error = 229
and got this:
Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.
Is there anything else I can do? My goal is to get an email notification when there's a permission denied error.... Seems like a bit of a step backwards to no longer allow you to choose which system messages you can log. Rather silly if you ask me - I thought getting these emails about permission denied errors was a pretty powerful thing - it didn't tell me the user that the error happened to, but it was still helpful. If anything I hoped I'd be able to get more info out of one of these alerts - not be completely unable to create the alert at all! Sorry - mini-rant. If anyone has any solutions/ideas, though, please let me know! Thanks.
December 12, 2006 at 3:57 pm
If I am not mistaken you can update the system table using DAC connection while system started in single user mode...
MohammedU
Microsoft SQL Server MVP
December 12, 2006 at 6:54 pm
Edit: I went back and read BOL.
Okay, I see what you mean. In setting alerts via error #, there's no text about what it will fire on and what it won't. However, when looking at setting an alert based on error level, it's clear that it will only fire on 19 and higher by default.
About the only thing that would make sense as a workaround is to run a trace... but it's not going to alert you in real time. You'll have to toggle the User Error Message event, but that should be all you need. You can clear out the T-SQL and Stored Procedure events because it'll show you the exact error message the user received.
There will be false positives (such as when a database context switch is made), but it's about the best option that's supported.
K. Brian Kelley
@kbriankelley
December 13, 2006 at 6:50 am
You can try event notification and listen on the event EXCEPTION. In your handling SP, filter out the exceptions you don't need, and send email etc.
December 13, 2006 at 10:03 am
Sounds like there is no easy solution. Brian - are you telling me that even if I do update the system table to "with_log" for that error code, that it still won't fire since it's not above a 19 severity?
It's a shame - I got the idea from reading various "Steps to securing your SQL Server" articles, one of which was written by our own Brian Knight, and got used to having it running. Oh well.
As for doing it through a trace - I would trace to a file and periodically review the file, right? I'm able to just trace events that error out? Sounds like that could be pretty useful - definitely something I should look into. I'm losing the real-time alerts, though, like you said, which was what I really appreciated about doing it the old way!
Peter - I'm not sure what you mean by setting up event notification & listening on the event exception. Is this notification services you're talking about? Haven't had the chance to dabble in that at all yet - seemed kind of clunky from what I saw of it in 2000, but maybe I should revisit it...
Thanks everyone for your help!
December 13, 2006 at 10:07 am
It's not notification service. Event notification is built in sql engine. It's a new feature in sql2005 utilizing sql service broker.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply