January 4, 2013 at 10:15 am
Is it possible to track when a database is detached or attached? You would think you could since it is like dropping or creating. But it definitely doesn't get tracked by just using the Create_Database or Drop_Database event types.
January 4, 2013 at 10:21 am
I just checked, and it does show up in my default trace
here's my query i use to get to the trace data:
/*
--Results
EventClassDescrip EventSubClassDescrip trace_event_id category_id name TextData
Audit DBCC Event NULL 116 8 Audit DBCC Event DBCC DETACHDB ([CDMMINOT])
*/
--SELECT * from sys.traces
declare @TraceIDToReview int
declare @path varchar(255)
SET @TraceIDToReview = 1 --this is the trace you want to review!
SELECT @path = path from sys.traces WHERE id = @TraceIDToReview
SELECT
TE.name As EventClassDescrip,
v.subclass_name As EventSubClassDescrip,
TE.*,
T.*
FROM ::fn_trace_gettable(@path, default) T
LEFT OUTER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
LEFT OUTER JOIN sys.trace_subclass_values V
ON T.EventClass = V.trace_event_id AND T.EventSubClass = V.subclass_value
Lowell
January 4, 2013 at 10:24 am
But in a ddl trigger? It doesn't look like I can but wanted to see if anyone knows better
January 4, 2013 at 10:44 am
yeah DBCC commands are not DDL Events; you can do it in a trace or event notification, though;
same thing happens when someone RESTORES a database...you cannot capture the RESTORE in a DDL trigger, you have to use event notifications and trackthat, i've got an example somewhere that does event notifications for these 4 events:
CREATE_DATABASE,
ALTER_DATABASE,
DROP_DATABASE,
AUDIT_BACKUP_RESTORE_EVENT
Lowell
January 4, 2013 at 10:48 am
Ok. Thanks. Just wanted to confirm. I'm trying to prevent people from restoring databases and dropping databases during business hours because it kills the plan cache. So I created a DDL trigger that will prevent the drops and creates. But not the other stuff.
January 4, 2013 at 10:53 am
thinking outside the box, i think you could create a script that adds users to a role that removes backup permissions,
on a per database level,
ie DENY BACKUP DATABASE TO [MyDomain\TypicalUserGroup], at the beginning of the day, and then removes them from the group at the end of the day;
that way, unless they are sysadmins, you prevent it from happening at all, simply with permissions.
Lowell
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply