question

If you're using SQL Server 2008 or better, you can read from the system health default extended events. Here's a description of how it works on the [Microsoft MSDN web site.][1] [1]: http://msdn.microsoft.com/en-us/library/ff877955.aspx

I have come up with two options the following one use the extended proc to look for offline messages in the log then from here the spid could be used to locate who carried out the action. sp_configure 'Show Advanced Options', 1 GO RECONFIGURE GO sp_configure 'Ad Hoc Distributed Queries', 1 GO RECONFIGURE GO IF OBJECT_ID('tempdb.dbo.#ErrorLog') IS NOT NULL DROP TABLE #ErrorLog SELECT * INTO #ErrorLog FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;', 'set fmtonly off exec master.sys.xp_readerrorlog') SELECT * FROM #ErrorLog WHERE TEXT LIKE '%Offline%' The second attempt is to look at the SQL executed recently to again look for offline events. SELECT session_id, TEXT, loginame, * FROM sys.dm_exec_connections con JOIN sysprocesses process ON con.session_id = process.spid CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS ST WHERE TEXT LIKE '%OFFL%' Both solution suffer from the fact that the SPID is reused quite quickly although the first query returns a row the associated SPID is probably doing something entirely different by the time it is run.

I use two queries to find the bad guy: ***1st*** query gives you the **time** (LogDate) when it happened: DECLARE @intermediate AS TABLE ( LogDate DATETIME NOT NULL ,ProcessInfo NVARCHAR(1024) NULL ,[Text] NVARCHAR(1024) NOT NULL ); DECLARE @FileList AS TABLE ( subdirectory NVARCHAR(4000) NOT NULL ,DEPTH BIGINT NOT NULL ,[FILE] BIGINT NOT NULL ); DECLARE @ErrorLog NVARCHAR(4000), @ErrorLogPath NVARCHAR(4000); SELECT @ErrorLog = CAST(SERVERPROPERTY(N'errorlogfilename') AS NVARCHAR(4000)); SELECT @ErrorLogPath = SUBSTRING(@ErrorLog, 1, LEN(@ErrorLog) - CHARINDEX(N'\', REVERSE(@ErrorLog))) + N'\'; INSERT INTO @FileList EXEC xp_dirtree @ErrorLogPath, 0, 1; DECLARE @NumberOfLogfiles INT; SET @NumberOfLogfiles = (SELECT COUNT(*)-1 FROM @FileList WHERE [@FileList].subdirectory LIKE N'ERRORLOG%'); WHILE @NumberOfLogfiles >= 0 BEGIN INSERT INTO @intermediate( LogDate ,ProcessInfo ,[Text] ) EXEC master.dbo.xp_readerrorlog @NumberOfLogfiles, 1; SET @NumberOfLogfiles = @NumberOfLogfiles - 1; END SELECT @@SERVERNAME AS InstanceName ,LogDate ,ProcessInfo ,[Text] FROM @intermediate WHERE NOT [@intermediate].Text IS NULL AND [@intermediate].Text LIKE N'%Offline%' ORDER BY [@intermediate].LogDate DESC; ***2nd*** query gives you the **name** (LoginName) DECLARE @enable INT; SELECT TOP 1 @enable = CONVERT(INT, value_in_use) FROM sys.configurations WHERE name = 'default trace enabled'; IF @enable = 1 --default trace is enabled BEGIN DECLARE @curr_tracefilename VARCHAR(500); DECLARE @base_tracefilename VARCHAR(500); DECLARE @indx INT; SELECT @curr_tracefilename = PATH FROM sys.traces WHERE is_default = 1; SET @curr_tracefilename = REVERSE(@curr_tracefilename); SET @indx = PATINDEX('%\%',@curr_tracefilename) SET @curr_tracefilename = REVERSE(@curr_tracefilename); SET @base_tracefilename = LEFT(@curr_tracefilename,LEN(@curr_tracefilename) - @indx) + '\log.trc'; SELECT Servername AS InstanceName ,ObjectName ,DatabaseName ,StartTime ,CASE ObjectType WHEN 8259 THEN 'CHECK' WHEN 8260 THEN 'DEFAULT' WHEN 8262 THEN 'FK' WHEN 8272 THEN 'SP' WHEN 8276 THEN 'TRIGGER Server' WHEN 8277 THEN 'TABLE' WHEN 8278 THEN 'VIEW' WHEN 16964 THEN 'DATABASE' WHEN 17235 THEN 'SCHEMA' WHEN 17475 THEN 'LOGIN' WHEN 17985 THEN 'FUNCTION (CLR-Aggregate)' WHEN 17993 THEN 'FUNCTION Inline' WHEN 18004 THEN 'FUNCTION' WHEN 19280 THEN 'PK' WHEN 20038 THEN 'FUNCTION Scalar' WHEN 20821 THEN 'UNIQUE' WHEN 21057 THEN 'ROLE Application' WHEN 21075 THEN 'SERVER' WHEN 21076 THEN 'TRIGGER SQL' WHEN 21313 THEN 'ASSEMBLY' WHEN 21333 THEN 'USER' WHEN 21572 THEN 'TRIGGER Database' WHEN 22868 THEN 'TYPE' WHEN 17232 THEN 'CLR Procedure' WHEN 21574 THEN 'CLR Table Valued Function' WHEN 21318 THEN 'CLR Scalar Function' WHEN 8275 THEN 'System Table' WHEN 22601 THEN 'INDEX' ELSE CAST(ObjectType AS VARCHAR(12)) + ' - ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.de/udb9/html/42f85c50-34c9-49ca-955f-af9595e2707f.htm' END AS ObjectTypeName ,LoginName ,ISNULL(ApplicationName, N'?') AS ApplicationName ,CASE EventClass WHEN 46 THEN 'CREATE' WHEN 47 THEN 'DROP' WHEN 164 THEN 'ALTER' WHEN 104 THEN 'SQL Login - SQL Login: ' + ISNULL(TargetLoginName, '?') + ', Action: ' + CASE EventSubClass WHEN 1 THEN 'ADD' WHEN 2 THEN 'DROP' ELSE '?' END WHEN 105 THEN 'Windows Login - Login: ' + ISNULL(TargetLoginName, '?') + ', Action: ' + CASE EventSubClass WHEN 1 THEN 'ADD' WHEN 2 THEN 'DROP' ELSE '?' END WHEN 108 THEN 'Srvrolemember - Role: ' + ISNULL(RoleName, '?') + ', Login: ' + ISNULL(TargetLoginName, '?') + ', Action: ' + CASE EventSubClass WHEN 1 THEN 'ADD' WHEN 2 THEN 'DROP' ELSE '?' END WHEN 109 THEN 'DBuser - User: ' + ISNULL(TargetUserName, '?') + ', Action: ' + CASE EventSubClass WHEN 1 THEN 'ADD' WHEN 2 THEN 'DROP' WHEN 3 THEN 'Grant database access' WHEN 4 THEN 'Revoke database access' ELSE '?' END WHEN 110 THEN 'DBRolemember - DB Role: ' + ISNULL(RoleName, '?') + ', DB User: ' + ISNULL(TargetUserName, '?') + ', Action: ' + CASE EventSubClass WHEN 1 THEN 'ADD' WHEN 2 THEN 'DROP' ELSE '?' END ELSE CAST(EventClass AS VARCHAR(3)) + ' - ?' END AS DDLoperation FROM ::fn_trace_gettable(@base_tracefilename,DEFAULT) WHERE (EventClass IN (164) AND EventSubClass = 0 AND objectType = 16964) -- 16964: DATABASE END When you finally compare Starttime and LogDate you'll get him!