May 14, 2009 at 2:28 pm
How do I see what system admin account was used to delete another user account?
SQL SERVER 2005 Enterprise
Window 2003 Enterprise
A user account was deleted from our SQL SERVER 2005 server and I would like to find out what account was used to execute the deletion. Is this event logged anywhere?
May 14, 2009 at 2:56 pm
I believe you can get that from the default trace. Security events are in the 104 - 114 range. You can also set up a server DDL trigger to detect those events, but that wouldn't help for your current situation.
Greg
May 15, 2009 at 9:27 am
Thanks, but I was hoping there was a way to see an event that has already happened. It seems odd that a user account could be deleted and Sql Server would not log it anywhere?!
May 15, 2009 at 10:09 am
Yes you can see that in the Default Trace, IF the trace files have not rolled over. You could run a query like this:
SELECT
TE.[name] AS event_name,
FTG.TextData,
FTG.BinaryData,
FTG.DatabaseID,
FTG.TransactionID,
FTG.LineNumber,
FTG.NTUserName,
FTG.NTDomainName,
FTG.HostName,
FTG.ClientProcessID,
FTG.ApplicationName,
FTG.LoginName,
FTG.SPID,
FTG.Duration,
FTG.StartTime,
FTG.EndTime,
FTG.Reads,
FTG.Writes,
FTG.CPU,
FTG.[Permissions],
FTG.Severity,
FTG.EventSubClass,
FTG.ObjectID,
FTG.Success,
FTG.IndexID,
FTG.IntegerData,
FTG.ServerName,
FTG.EventClass,
FTG.ObjectType,
FTG.NestLevel,
FTG.State,
FTG.Error,
FTG.Mode,
FTG.Handle,
FTG.ObjectName,
FTG.DatabaseName,
FTG.FileName,
FTG.OwnerName,
FTG.RoleName,
FTG.TargetUserName,
FTG.DBUserName,
FTG.LoginSid,
FTG.TargetLoginName,
FTG.TargetLoginSid,
FTG.ColumnPermissions,
FTG.LinkedServerName,
FTG.ProviderName,
FTG.MethodName,
FTG.RowCounts,
FTG.RequestID,
FTG.XactSequence,
FTG.EventSequence,
FTG.BigintData1,
FTG.BigintData2,
FTG.GUID,
FTG.IntegerData2,
FTG.ObjectID2,
FTG.[Type],
FTG.OwnerID,
FTG.ParentName,
FTG.IsSystem,
FTG.Offset,
FTG.SourceDatabaseID,
FTG.SqlHandle,
FTG.SessionLoginName,
FTG.PlanHandle
FROM
sys.traces AS 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) AS FTG JOIN
sys.trace_events AS TE
ON FTG.EventClass = TE.trace_event_id
WHERE
T.is_default = 1 AND
(
TE.[name] = 'Audit Add DB User Event' AND
FTG.EventSubClass = 2 -- this is drop
) OR
(
TE.[name] = 'Audit Addlogin Event' AND
FTG.EventSubClass = 2 -- this is drop
)
Pick the columns you are interested in.
Edit: I didn't close the bold tag so it looked funny.
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
May 15, 2009 at 11:09 am
Thank you this worked perfectly!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply