Getting SQL Server Error Messages to Write to the Event Log

SQL Server alerts can give you valuable insight into issues with your servers and can track a multitude of different errors, messages and events. To view all the built-in system error messages, run the following script on Master database.

SELECT *

  FROM SYS.MESSAGES

 WHERE LANGUAGE_ID = 1033

1033 is the English language ID taken from the syslanguages table

SELECT MSGLANGID

  FROM SYSLANGUAGES

 WHERE NAME = 'US_ENGLISH'

In SQL Sever 2008 there are over 8900 different messages that can be returned from the server, all with varying severities and text messages. The event I’m going to focus on here is message_id 229, “The %ls permission was denied on the object '%.*ls', database '%.*ls', schema '%.*ls'”.

SELECT * FROM SYS.MESSAGES

 WHERE LANGUAGE_ID = 1033

   and message_id = 229

Message_id	Language_id	Severity	Is_event_logged	Text
229	1033	14	0	The %ls permission was denied on the object '%.*ls', database '%.*ls', schema '%.*ls'.

If I want to be alerted any time a user tries to call a stored procedure or query a table they dont have access to, I can create a SQL Agent Alert using the following procedure and further configuring the alert to email me.

EXEC msdb.dbo.sp_add_alert @name=N'Insufficient Permission Error',

            @message_id=229,

            @severity=0,

            @enabled=1,

            @delay_between_responses=0,

            @include_event_description_in=1,

            @category_name=N'[Uncategorized]',

            @job_id=N'00000000-0000-0000-0000-000000000000'

GO

After this is setup, you would expect that everytime this error is generated an email would be sent to you, but…… going back to the IS_EVENT_LOGGED column in the SYS.MESSAGES table, you see that this event is not logged and will not be sent to the Event Log.

So how do I get this error to log to the Event Log and then sent in an email? In SQL 2005 SP2+ and SQL 2008, the following command can be issued to modify the existing messages:

EXEC sp_altermessage

 @message_id = 229

,@parameter = 'WITH_LOG'

,@parameter_value = 'True'

Setting the parameter ‘With_Log’ to a value of true tells SQL that the triggering of this event will always result in the error being written to the Event Log

NOTE: If/when you decide that you no longer want to monitor this event, just disabling the alert will not be enough to keep the event from being logged to the Event Log. You must run the following script to keep it from being logged.

EXEC sp_altermessage

 @message_id = 229

,@parameter = 'WITH_LOG'

,@parameter_value = 'False'