Results 1 to 4 of 4
  1. #1
    Join Date
    May 2011
    Posts
    9

    Unanswered: who dropped SQL Server 2008R2 login

    Hi,

    Is it possible to discover who deleted an login account for a SQL 2008 R2 server please?

    This account was an AD account which had dbowner rights to a database

    Kind regards

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Do you have a DDL trigger for this event?

    I don't think it would be possible to find out "who dunnit" without this in place.
    George
    Home | Blog

  3. #3
    Join Date
    May 2011
    Posts
    9
    Hi, we didn't have trigger setup but the default trace was. This script resolved our question and provided by Andrew Bainbridge on the SQL forums

    DECLARE @TF varchar(4000)
    SELECT @TF = CONVERT(varchar(4000),value) FROM ::fn_trace_getinfo(0) WHERE traceid=1 AND property=2

    SELECT LoginName,
    StartTime,
    TargetLoginName,
    CASE EventSubClass
    WHEN 1 THEN 'CREATE LOGIN'
    WHEN 2 THEN 'DROP LOGIN'
    END AS 'Log Event'
    FROM ::
    FN_TRACE_GETTABLE(@TF, 0)
    INNER JOIN sys.trace_events e ON eventclass = trace_event_id
    WHERE e.trace_event_id = 105

  4. #4
    Join Date
    Aug 2008
    Posts
    147
    To see the full list of events on the default trace possible - use SELECT * FROM sys.trace_events

    Keep in mind with the default trace - by default it will rollover and replace the files. So , if you want historical infomation you need to set up a job to move the file to an archive
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •