The mystery of the dropped login

Question

The mystery of the dropped login

sean redmond

SSCertifiable

Points: 5829
More actions
January 6, 2010 at 8:31 am

#215202

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

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

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

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

sean redmond SSCertifiable Points: 5829 More actions · Answer 2

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.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

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

The mystery of the dropped login

Cookies on SQLServerCentral