October 5, 2007 at 1:24 am
I want to audit for all the tables and stored procedure in my database with the date as who has changed the difinition and which field is changed. is there any way to audit and insert into a table other than running a sql profiler. in this environment i cannot run sql profiler always. so how is it audited in my absence also.
October 5, 2007 at 1:51 am
If your using SQL 2005 you can use DDL Triggers, Event Notification or even WMI alerts. It mainly depends if you just want to audit the event on the same server, another server or maybe even sending a notification to the administrators as soon as something changes. You can find moere details about them in BOL.
Another built-in option is the default trace which captures any schema changes and the Schema change History report.
One thing to keep in mind is depending on the way an application connects, it's not always possible to identify the actual user.
Hope this helps
Markus
[font="Verdana"]Markus Bohse[/font]
October 5, 2007 at 3:40 am
what is BOL can you explain on how to do these auiding through ddl
October 5, 2007 at 5:15 am
Hi
BOL means Books Online. Press F1 from management studio to get the help where you can search for DDL Triggers.
Check out some sites, get a basic idea of things.
"Keep Trying"
October 5, 2007 at 6:27 am
Yes, DDL triggers are the way to go here. Make sure you understand XQuery to understand the output.
*shameless self promotion alert*
I recently contributed on a SQL Server 2005 security book which has a chapter on DDL triggers. Included are examples of how to audit for these types of changes. The link is in my signature.
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply