Today I faced a new quest that is to raise an alarm incase if any new database is created in the server.
I have scouted in Google as a first step J but didn’t find any script meeting my requirements so wrote a script of my own.
So the best way I thought of achieving this is through server level DDL trigger which gets fired automatically when anyone creates a new database. I used msdb.dbo.sp_send_dbmail inside the trigger to send mail to respective recepients. In the mail being sent I need information like when the database was created, what is the recovery model of it, who is the owner of the database, collation. Based on requirement we can get other information as well.
IF EXISTS (SELECT * FROM sys.server_triggers
WHERE name = 'Alert_Database_Creation')
DROP TRIGGER Alert_Database_Creation
ON ALL SERVER;
CREATE TRIGGER Alert_Database_Creation
ON ALL SERVER
ASDeclare @sub varchar(2000),@srv varchar(2000),@dbname varchar(2000),@bod varchar(2000)
Declare @recoverymodel varchar(2000),@collation varchar(2000),@datecreated varchar(2000),@owner varchar(255)
select @srv= @@SERVERNAME
select @dbname = name,@owner = SUSER_SNAME(owner_sid), @collation = collation_name,@datecreated =
(SELECT CONVERT(VARCHAR(20), create_date, 100))
,@recoverymodel= recovery_model_desc from sys.databases
where (DATEPART(DD,create_date) = DATEPART(DD,GETDATE())) AND (DATEPART(YYYY,create_date) = DATEPART(YYYY,GETDATE()))
AND (DATEPART(MM,create_date) = DATEPART(MM,GETDATE()))
select @sub = 'New Database Has Been Created in server '+ @srv
Select @bod = 'Database Name : '+ UPPER(@dbname) + Char(13)+'Database Owner : '+@owner+ CHAR(13)+'Recovery Model : '+@recoverymodel
+CHAR(13)+'Collation : '+@collation+CHAR(13)+'Created Date : '+@datecreated
@profile_name = 'profile name',
@recipients = 'email@address’,
@body = @bod,