Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 7 down vote accepted

@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)[1]','NVARCHAR(255)'),
    @login = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','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 = N'you@yourdomain.com',
    @subject = N'Somebody done dropped a database',
    @body = @msg;
END
GO
share|improve this answer

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.

share|improve this answer
    
I would probably whittle down this query to just filter for event = dropped and objecttype = database. –  Aaron Bertrand Apr 22 at 20:12
    
@AaronBertrand changed the query to only show Create and Drop database events. Will leave upto OP to modify as per need. –  Kin Apr 22 at 20:21
    
Just to note: Don't know your setup. But if all people use same database user (which is quite common) you may only be able to determine either by user host or other parameter only or not at all. –  Gacek Apr 23 at 12:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.