Post reply

Get an email when a database is Created/Altered/Dropped?

  • June 30, 2011 at 12:23 pm

    #252935

    Hi,

    what we have to do to get an email when a database is Created/Altered/Dropped?

    I already have database mail set up in-place.

    Thanks

  • June 30, 2011 at 12:35 pm

    #1346698

    pshaship (6/30/2011)

    Hi,

    what we have to do to get an email when a database is Created/Altered/Dropped?

    I already have database mail set up in-place.

    Thanks

    DDL trigger

  • June 30, 2011 at 1:03 pm

    #1346708

    Thanks,

    I have created the trigger as below and I'm getting the email once the database is created. But I'm looking to get an email

    before the users attempts to create the database and NOT allow him to create it(Just send a notification to user that contact DBA before creating the database). How to achieve this?

    IF EXISTS (SELECT * FROM sys.server_triggers

    WHERE name = 'ddl_trig_database')

    DROP TRIGGER ddl_trig_database

    ON ALL SERVER;

    GO

    CREATE TRIGGER ddl_trig_database

    ON ALL SERVER

    FOR CREATE_DATABASE

    AS

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'Databasemail',

    @recipients = 'abc@ciginsurance.com',

    @body = 'Database created?.',

    @subject = 'Database created';

    GO

  • June 30, 2011 at 1:12 pm

    #1346711

    Have you tried a ROLLBACK in the trigger? From the docs it looks like you could. Also drops can be handled the same way, but you might also add some check to see whether the user is a member of an AD group like the database admins and allow it in that case..

    CEWII

  • July 1, 2011 at 8:32 am

    #1347051

    I second what Elliott has stated and also add that if someone is creating/dropping or altering databases then you should review security and permissions and "nip it in the bud" at source.

    ________________________________________________________________________________

    Can I ask you a rhetorical question...?
    ________________________________________________________________________________

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply