I have already a trigger that sends us a mail when a new database is created (some of our users are db_creator). Here's the code:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
create trigger [newdb]
on ALL Server
WITH EXECUTE AS 'sa'
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)', 'varchar(256)') + ' created. Check if database mirroring is necessary.'
SET @sujet = 'NEW DATABASE CREATED ON INSTANCE ' + @instance_name
@recipients = 'firstname.lastname@example.org',
@body = @message,
@subject = @sujet ;
PRINT 'Trigger Created'
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF
ENABLE TRIGGER [newdb] ON ALL SERVER
And it works perfectly.
Well, our backup software needs some rights to backup and restore objects in our instance (for example: item restore in sharepoint).
So I need to give db_backupoperator database role to our backup user whenever a new database is created by the cutsomer.
I was thinking of modifying my trigger by adding some code like:
CREATE USER [domain\bck_user] FOR LOGIN [domain\bck_user] ;
EXEC sp_addrolemember N'db_backupoperator', N'domain\bck_user' ;
But obviously, the 'USE' doesn't not work into triggers and therefore I always give my bck user the DB role db_backupoperator to the "master" DB :-(
So how can I automate the "grant db_backupoperator" for my bck user ?
I would really appreciate if someone has an idea to solve my problem :-P
Thanks in advance.
Have a nice day.