SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create trigger [newdb]
on ALL Server
WITH EXECUTE AS 'sa'
for CREATE_DATABASE
as

PRINT 'Creating Trigger: newDB'

set nocount on
 
declare @data xml
declare @message varchar(1000)
declare @instance_name varchar(30)
declare @sujet varchar(150)
set @data = EVENTDATA()
--set @instance_name = 
select @instance_name = @@ServerName

SET @message = 'New DB: ' + @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)') + ' created. Check if database mirroring is necessary.'
SET @sujet = 'NEW DATABASE CREATED ON INSTANCE ' + @instance_name
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'email@contoso.com',
@body = @message,
@subject = @sujet ;

PRINT 'Trigger Created'

GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [newdb] ON ALL SERVER
GO

USE ?
CREATE USER [domain\bck_user] FOR LOGIN [domain\bck_user] ; 
EXEC sp_addrolemember N'db_backupoperator', N'domain\bck_user' ;

DECLARE @SQL NVARCHAR(MAX) = N'
EXEC [YourDatabase]..sp_executesql N''CREATE USER [domain\bck_user] FOR LOGIN [domain\bck_user]''';

EXEC sp_executesql @SQL;

SET @SQL = N'
EXEC [YourDatabase]..sp_executesql N''EXEC sp_addrolemember N''''db_backupoperator'''', N''''domain\bck_user''''''';

EXEC sp_executesql @SQL;