asked
"Create Database"notification only via ddl_trigger?
Hi all, Just a quick one: I want to set up a notification (alert ...whatever) for create database events. In a test environment I'm already using a ddl trigger, which sends an emial to me when a database is created. My question: are there any alternatives, smarter solutions? Regards Dirk
I think that was the sentence I wanted to hear. "DDL trigger is about as smart as you can get". I still have to deal with a few applications which set up databases on their own (don't ask....) but without some users/groups needed for support purposes. Either the trigger will just notify me or runs a proc, which does the additional setup for me.
What are you trying to achieve? I would have thought that a SQLServer-generated notification on a DDL trigger is about as smart as you can get. I can think of other things to do (eg checking for any databases created after TempDB), but those are checks rather than alert-generators. What's your ideal / goal here?
Users/groups needed for support purposes? Are these all the same? And needed for all databases? In which case, why not put it in the `model` database?
We does something with the CREATE_DATE in sys.database for a report we produce everyday. It simply reports on the DB/Tables/SP created in the previous day. Not real time though and may not be smarter, but works for us.
Depending on what version of SQL you are using, have a look at CDC (available in SQL 2008). Not sure if it logs when a database is created, but it keep tracks of any changes made to a table:
https://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-(cdc)-in-sql-server-2008/
16 People are following this question.
