question

DirkHondong avatar image
DirkHondong 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
triggernotificationcreate-databaseddl-changes
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
1 Like 1 ·
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?
0 Likes 0 ·
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?
0 Likes 0 ·
sp_lock avatar image
sp_lock answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ruancra avatar image
ruancra answered
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/
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.