October 29, 2012 at 3:45 pm
Hi All,
I have looked all over the internet and have not found anything that comes close to answering my question. I need to know if the sql server or windows event logs capture when a trigger is disabled/enabled. I figured this would be a simple search on Google but I was apparently mistaken. Any guidance is greatly appreciated.
October 29, 2012 at 7:34 pm
Not directly it doesn't. The default trace will log the create or drop of the trigger and it will log an alter table if the trigger is disabled or enabled.
The below code will return the information in the default trace with a bit of filtering
declare @DefaultLog nvarchar(500)
select @DefaultLog = cast(value as nvarchar(500)) FROM ::fn_trace_getinfo(0)
where traceid = 1 and property = 2
SELECT loginname, EventClass, loginsid, spid, hostname, applicationname, servername, databasename,
objectName, TargetUsername,RoleName, TargetLoginName , e.category_id,
cat.name as [CategoryName], textdata, starttime, eventclass, eventsubclass,--0=begin,1=commit
e.name as EventName
FROM ::fn_trace_gettable(@DefaultLog,0)
INNER JOIN sys.trace_events e ON eventclass = trace_event_id
INNER JOIN sys.trace_categories AS cat ON e.category_id = cat.category_id
WHERE databasename not in ('tempdb','msdb','master','model') AND
(objectname IS not NULL or TargetLoginName is not null) AND --filter by objectname
-- e.category_id = 5 AND --category 5 is objects
applicationname not like 'SQLAgent%'
-- and e.trace_event_id in (46,47,164,102,103,104,105,106,108,109,110,111) -- filer by event_id
This is a table of the events captured by the default trace. I can't remember where I got, but it is out there somewhere.
event_id description
18 Audit Server Starts And Stops
20 Audit Login Failed
22 ErrorLog
46 Object:Created
47 Object:Deleted
55 Hash Warning
69 Sort Warnings
79 Missing Column Statistics
80 Missing Join Predicate
81 Server Memory Change
92 Data File Auto Grow
93 Log File Auto Grow
94 Data File Auto Shrink
95 Log File Auto Shrink
102 Audit Database Scope GDR Event
103 Audit Schema Object GDR Event
104 Audit Addlogin Event
105 Audit Login GDR Event
106 Audit Login Change Property Event
108 Audit Add Login to Server Role Event
109 Audit Add DB User Event
110 Audit Add Member to DB Role Event
111 Audit Add Role Event
115 Audit Backup/Restore Event
116 Audit DBCC Event
117 Audit Change Audit Event
152 Audit Change Database Owner
153 Audit Schema Object Take Ownership Event
155 FT:Crawl Started
156 FT:Crawl Stopped
157 FT:Crawl Aborted
164 Object:Altered
167 Database Mirroring State Change
175 Audit Server Alter Trace Event
218 Plan Guide Unsuccessful
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply