InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 06:14:01 GMT20http://www.sqlservercentral.com/Forums/Topic1370378-392-1.aspxThat's even easier ! How didn't I think that at first :doze: ...Wed, 10 Oct 2012 02:08:42 GMTfranck.matonhttp://www.sqlservercentral.com/Forums/Topic1370378-392-1.aspxOr just add the required permission(s) to the model db. Every db created after that will automatically have the same permission(s).Tue, 09 Oct 2012 11:47:40 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1370378-392-1.aspxHum, well, it works perfectly, many thanks :-)Franck.Tue, 09 Oct 2012 08:21:27 GMTfranck.matonhttp://www.sqlservercentral.com/Forums/Topic1370378-392-1.aspx[code="sql"]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;[/code]Tue, 09 Oct 2012 08:05:06 GMTSean Pearcehttp://www.sqlservercentral.com/Forums/Topic1370378-392-1.aspxHello,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:[code="sql"]SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate trigger [newdb]on ALL ServerWITH EXECUTE AS 'sa'for CREATE_DATABASEasPRINT 'Creating Trigger: newDB'set nocount on declare @data xmldeclare @message varchar(1000)declare @instance_name varchar(30)declare @sujet varchar(150)set @data = EVENTDATA()--set @instance_name = select @instance_name = @@ServerNameSET @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_nameEXEC msdb.dbo.sp_send_dbmail@recipients = 'email@contoso.com',@body = @message,@subject = @sujet ;PRINT 'Trigger Created'GOSET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER OFFGOENABLE TRIGGER [newdb] ON ALL SERVERGO[/code]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:[code="sql"]USE ?CREATE USER [domain\bck_user] FOR LOGIN [domain\bck_user] ; EXEC sp_addrolemember N'db_backupoperator', N'domain\bck_user' ;[/code]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 :-PThanks in advance.Have a nice day.Franck.Tue, 09 Oct 2012 07:12:43 GMTfranck.maton