March 13, 2007 at 2:44 pm
When I set up an alert for error 229 "permission denied", the alert never gets generated, even though I get the following error when connected through management studio:
Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'myobject', database 'mydatabase', schema 'dbo'.
I have the type set to SQL Server event alert, the error number is 229, with myself as the notify operator. Under History, there are no occurrences of the alert.
I am guessing that the root of the problem is that the error is not logged to the event log.
What are my options here?
Regards,
Rubes
March 16, 2007 at 8:00 am
This was removed by the editor as SPAM
March 17, 2007 at 6:19 pm
What level of access to do you have on this ser ver?
MohammedU
Microsoft SQL Server MVP
March 20, 2007 at 9:48 am
My windows account has sysadmin permissions and local administrator permissions.
Sql server and sql server agent are running under a domain account that has sysadmin permissions and local administrator permissions.
Regards,
Rubes
April 3, 2007 at 2:31 pm
The following 2 excerpts are from BOL. Under SQL 2000 it was required to run sp_altermessage on an error number for it to get logged to the Windows Application log and therefore get picked up the Agent which reads the Application Log. It seems that this functionality has been removed as viewing the code of sp_altermessage shows that it will generate an error on any error id less than 50000. Why Microsoft would remove this vital functionality is beyond me.
It seems that the only available option is to use a SQL trace that captures errors to a SQL table then have a job read that table and act accordingly.
These are the circumstances under which errors/messages generated by SQL Server and SQL Server applications are sent to the Windows application log and can therefore raise alerts:
sp_altermessage cannot be used to change the logging behavior of system messages (messages with Message ID < 50000). To audit system messages, use SQL Trace and the User Error Message Event Class. For more information, see Introducing SQL Trace.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply