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;
GO
CREATE TRIGGER Alert_Database_Creation
ON ALL SERVER
FOR
CREATE_DATABASE
AS
Declare
@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
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'profile name',
@recipients =
'email@address’,
@body =
@bod,
@subject =@sub
Thanks!!
