SQL Server Alert

[areebasad]

I want to monitor the tables of an SQL Server Database, i mean if some one made changes on a spicific table i want an alert to send me an email. What event should i monitor?

[RSAAdmin]

Hello areeb,

It is very difficult to determine the message ID that should be monitored in this way.  My suggestion would be to create a Test Case.  Create a spreadsheet with the following column headings and fill in the information needed to perform the test:

Test Criteria: Determine that message id used when a change is made to a SQL table

Test Target: SQL table that will be modified

Test Input: 'username' that will be used to create events that indicate a table has been changed in SQL

Test Time: Date/Time when the action will be tested

Once the above is in place you can perform a realtime analysis using filters to show only events taking place on the specified table, by the specified user, during the timeframe that was decided upon.  Then you can see exactly what message ID was used for those actions.

After the Message ID is determined you can create alerts or reports to show when those activities take place.

Good luck,

Steve

[RSAAdmin]

Hello Areeb,

I do pull Report for SQL Changes using the query below

DeviceAddress in (select paddr from device_list where dtype=65 AND DeviceAddress=${DatabaseName}  ) AND( ReferenceID  IN  ('104','108','109','110','111','112','115','117','118','128','129''177','175') AND (UserName NOT LIKE 'audit_reader'))

$Database is a variable I use for Different Database Servers I have.

http://pic.dhe.ibm.com/infocenter/tivihelp/v2r1/index.jsp?topic=%2Fcom.ibm.itcim.doc%2Ftcim85_instal...

The above IBM link was very handy to chose whihc event ID I should monitor

Hope this helps.

Regards

Afeef

[mrbeanus]

Does anyone have own best practice list of MSSQL event audit message? I'm asking cause in IBM reference there is a lot of messages which duplicate himself.

For example problem is when someone create database then you get several MSSQL events, question is which one is the most best for us.

Anyone tried to assign sysadmin role for a user? This is event 108 and in parsed DATABASE table there is no difference when administrator add role sysadmin to user and remove this role. So you can't create correlation rule for adding this role to some user ;-/

Two event are the same... in eventviewer there is a difference

When you add role sysadmin to user there is information about database = dbo

When you remove this role from user there is no notice about database = dbo

This is only one difference. Problem is when message is parsed into DATABASE table, this two changes(add / remove)  looks the same. In parsed DATABASE table there is no any information about database "dbo"