January 6, 2010 at 8:31 am
Hi everyone
A fellow's login was dropped between Christmas and New Year and I'm looking to be pointed in the right direction as to where I can find info as to who did it.
I expected it (rather naively) to be the SQL Server 2005 Logs. I'm now looking inside the transaction log (::fn_dblog is very useful) for records with the transaction name of 'DROP LOGIN'.
Am I missing a very obvious source and if so, would someone let me know please
Many thanks
January 7, 2010 at 7:02 am
You can use the Default Trace, IF it has logs back far enough as it only has up to 100MB of data. Here's a query that should find it, if your default trace files go back far enough:
;WITH cteEventSubClasses AS
(
SELECT
TSV.trace_event_id,
TSV.subclass_name,
TSV.subclass_value
FROM
sys.trace_subclass_values AS TSV JOIN
sys.trace_columns AS TC ON
TSV.trace_column_id = TC.trace_column_id
WHERE
TC.[name] = 'EventSubClass'
)
SELECT
TE.NAME AS EventName,
Convert(nvarchar(10), I.EventSubClass) + N'-' + ESC.subclass_name as EventSubClass,
I.ApplicationName,
I.HostName,
I.SessionLoginName, /* add this in case they use EXECUTE AS */
I.LoginName AS LoginMakingChange,
I.TargetLoginName AS AlteredLogin,
I.RoleName AS AlteredServerRole,
I.StartTime
FROM
sys.traces T CROSS Apply
sys.fn_trace_gettable(CASE WHEN CHARINDEX('_', T.[path]) <> 0
THEN SUBSTRING(T.PATH, 1, CHARINDEX('_', T.[path]) - 1) + '.trc'
ELSE T.[path]
End, T.max_files) I JOIN
sys.trace_events AS TE ON
I.EventClass = TE.trace_event_id LEFT JOIN
cteEventSubClasses AS ESC ON
TE.trace_event_id = ESC.trace_event_id AND
I.EventSubClass = ESC.subclass_value
WHERE
T.is_default = 1 ANd
TE.NAME = 'Audit AddLogin Event'
The Audit AddLoginEvent also shows drops, check the EventSubClass.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 8, 2010 at 7:59 am
Thanks very much for the script! It is extremely useful and it's good to learn about new DB objects. I see now that I'm much more ignorant about traces than I realised.
January 8, 2010 at 8:17 am
Glad I could help.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply