December 18, 2008 at 7:02 am
Guys and Gals,
How you can help. I have a situation that I need a resolution to. A login was deleted by possibly a member of the IS staff and I need to track down who and when. Is there any built in way in SQL 2005 to do this. I have already checked the Event and SQL Logs. Is there a history table of sorts in the master database that I can query against?
December 18, 2008 at 7:12 am
It'll be recorded in the default trace. There are 5 (or so) trace files in the SQL error log directory, that's the default trace (providing you haven't turned it off)
The trace stores max 5 20MB files. Once it fills the 5th file, it deletes the earliest and creates a new one. Hence the default trace only stores a certain amount of info. How long the history is stored depends on how active the server is.
There's no history table stored anywhere, you can set something like that up with DDL triggers if you want, but you have to do it manually.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 18, 2008 at 7:20 am
Thxs for the tip .. I will look there. I had read up on setting up a trigger on for add delete and modifications of logins.
December 18, 2008 at 7:29 am
To do this you can get the current def trace file name by
SELECT value FROM ::fn_trace_getinfo(0) where property = 2
then substitute the filename, and search for events 104 (indicating logins dropped or created)
SELECT *
FROM ::FN_TRACE_GETTABLE('C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log_6.trc',
0)
WHERE EventClass = 104 -- when logins are added or deleted
Regards,
Andras
December 18, 2008 at 8:47 am
Thanks everyone for the tip... I was able to find what I was hoping I would find..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply