Automating security within triggers

  • Hello,

    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

    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

    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:

    USE ?

    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 😛

    Thanks in advance.

    Have a nice day.

    Franck.

  • 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;

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • Hum, well, it works perfectly, many thanks 🙂

    Franck.

  • Or just add the required permission(s) to the model db. Every db created after that will automatically have the same permission(s).

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • That's even easier ! How didn't I think that at first :doze: ...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply