Find out who droped\deleted MS SQL Server database
you must have below configuration to find out who deleted SQL Server object.
sp_configure 'show
advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'default trace enabled', 1;
GO
RECONFIGURE;
once above configuration done. you can do some test like create database , drop database, create some table , drop table, create stored procedure and drop them
With cteobjypes 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] = 'ObjectType'
),
cteEvntsbcls 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], X.ApplicationName,X.ClientProcessID,X.ColumnPermissions,X.DatabaseID,X.DatabaseName,X.Duration,X.EndTime,X.Error,X.EventSequence,Convert(nvarchar(10), X.EventSubClass) + N'-' + ESC.subclass_name as EventSubClass,X.FileName,
X.HostName,X.IndexID,X.IntegerData,X.IsSystem,X.LoginName,X.LoginSid,X.NestLevel,X.NTDomainName,X.NTUserName,X.ObjectID,
X.ObjectID2,X.ObjectName,Convert(nvarchar(10), X.ObjectType) + N'-' + OT.subclass_name as ObjectType,X.OwnerName,
X.ParentName,X.Permissions,X.RequestID,X.RoleName,X.ServerName,X.SessionLoginName,X.SPID,X.StartTime,X.State,X.Success,X.TargetLoginName,X.TargetLoginSid,X.TargetUserName,X.TextData,
X.TransactionID,X.Type
FROM
sys.traces T CROSS Apply
sys.fn_trace_gettable('M:\MSSQL10_50.MOSSSP\MSSQL\Log\log_4160' + '.trc'
, T.max_files) x JOIN
sys.trace_events AS TE ON
X.EventClass = TE.trace_event_id LEFT JOIN
cteEvntsbcls AS ESC ON
TE.trace_event_id = ESC.trace_event_id And
X.EventSubClass = ESC.subclass_value LEFT JOIN
cteobjypes AS OT ON
TE.trace_event_id = OT.trace_event_id AND
X.ObjectType = OT.subclass_value
WHERE
T.is_default = 1 AND
TE.NAME = 'Object:Deleted'
and Convert(nvarchar(10), X.ObjectType) + N'-' + OT.subclass_name like '%16964-db%'
now you can find out who dropped or tampered with database or SQL Server objects
Thanks & Regards
Jayant Dass
MS SQL Server DBA
