Header

Wednesday, March 26, 2014

Monitoring if new databases are created

Want an email when somebody is creating a new database on your SQL Server ?

--- CREATE TRIGGER ON DATABASE CREATION
CREATE TRIGGER DDL_TRIG_CREATE_DATABASE
ON ALL SERVER 
FOR CREATE_DATABASE 
AS 
 DECLARE @ServerName nvarchar(255)
 DECLARE @Cmd nvarchar(max)
 DECLARE @DBName nvarchar(255)
 DECLARE @Subj nvarchar(2000)
 DECLARE @XML XML
 DECLARE @Recipients nvarchar(255)
 DECLARE @MailProfile nvarchar(255)

 SET @Recipients = 'you@yourcomapny.dk'
 SET @XML = EVENTDATA()
 SET @Cmd = CAST(@XML as nvarchar(MAX))
 SET @ServerName = @@SERVERNAME
 SET @DBName = @XML.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(200)') 
 SET @Subj = 'New database "' + @DBName + '" created on ' + @@SERVERNAME + ' by ' + SUSER_SNAME()
 
 -- Find the database mail profile name
 create table #dbmail_profile
 (
  profileid int null,
  profilename varchar(125) null,
  accountid int null,
  accountname varchar(125) null,
  sequencenumber int null
 )
 insert #dbmail_profile
 EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

 select @MailProfile = profilename
 from #dbmail_profile
 where sequencenumber = 1

 drop table #dbmail_profile

 EXEC msdb.dbo.sp_send_dbmail
  @profile_name = @MailProfile,
  @recipients = @Recipients,
  @subject = @Subj,
  @body_format = 'TEXT',
  @body = @Cmd;

No comments: