June 30, 2011 at 12:23 pm
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
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
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
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
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