event notification

Question

event notification

sqlstar2011

SSCertifiable

Points: 6000
More actions
May 26, 2011 at 11:47 am

#238886

I have created a event notification mechanism to identify any changes to the server roles. I am able to capture
Event Type Server Name Login Date
ADD_ROLE_MEMBERDevBoxtestuser2011-05-26 13:15:56.723
I am using following query to grab the record above
@EventMessage.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(128)' ),
@EventMessage.value('(/EVENT_INSTANCE/ServerName)[1]', 'varchar(128)' ),
@EventMessage.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(128)' ),
GetDate()
would like to find out if its possible to capture the actual action.. lets say if some was made db_creator.. I would like to add a column to show that information. which event_instance item should I use to grab that information?
any thoughts?
thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

You clould find some info on the web, but I propose to just capture the full xml to investigate the availability of data and then have a look at bol or the web for more details (targeted)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 2

use Database Audit Specification against particular object or schema,you can capture all stuff

http://msdn.microsoft.com/en-us/library/cc280386.aspx

http://technet.microsoft.com/en-us/library/cc280404.aspx

My Blog
www.aureus-salah.com

LutzM SSC Guru Points: 107049 More actions · Answer 3

Syed Jahanzaib Bin hassan (5/27/2011)

use Database Audit Specification against particular object or schema,you can capture all stuff

http://msdn.microsoft.com/en-us/library/cc280386.aspx

http://technet.microsoft.com/en-us/library/cc280404.aspx%5B/quote%5D

Please note this is the SQL 2K5 forum....

Or is the intention of your reply different than to answer the question? (...)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]