I have in the
SQL server a lot of databases, but one of them has been dropped, so I need to create a trigger to send me an email when someone try to drop another database. The e-mail should contain the user name and the the name of the database.
I have in the
@Kin is right about getting this info from the default trace, if it is in fact still there (depends on how busy your server is, and how long ago this happened).
To get an e-mail immediately, though, instead of having to remember to check the default trace all the time just in case something happened, you can do this from a DDL trigger. Database Mail config is out of scope for the question, I think, so I'll leave that part to you (there is a Q & A here).
ALTER TRIGGER DroppinDatabasesAwYeah ON ALL SERVER FOR DROP_DATABASE AS BEGIN DECLARE @name NVARCHAR(255), @login NVARCHAR(255), @msg NVARCHAR(MAX); SELECT @name = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)','NVARCHAR(255)'), @login = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)','NVARCHAR(255)'); SELECT @msg = N'It appears that ' + QUOTENAME(COALESCE(@login, N'<unknown>')) + N' has dropped the database ' + QUOTENAME(COALESCE(@name, N'<unknown>')) + N'.'; EXEC msdb.dbo.sp_send_dbmail @profile_name = N'default', @recipients = Nemail@example.com', @subject = N'Somebody done dropped a database', @body = @msg; END GO
If you have default trace running and hopefully it is not recycled, it will be logged in there :
SELECT SPID ,LoginName ,NTUserName ,NTDomainName ,HostName ,ApplicationName ,StartTime ,ServerName ,DatabaseName ,CASE EventClass WHEN 46 THEN 'CREATE' WHEN 47 THEN 'DROP' ELSE 'OTHER' END AS EventClass ,CASE ObjectType WHEN 16964 THEN 'DATABASE' ELSE 'OTHER' END AS ObjectType FROM [fn_trace_gettable](CONVERT(VARCHAR(150), ( SELECT TOP 1 value FROM [fn_trace_getinfo](NULL) WHERE [property] = 2 )), DEFAULT) T WHERE ObjectType = 16964 /* Database */ AND EventSubClass = 1 /* Committed */ AND StartTime > DATEADD(HH, - 24, GETDATE()) -- get for last 24 hrs Only !! ORDER BY StartTime GO
A sort of similar question was answered HERE. The underlying concept to trace down is same.