Loop and Create Triggers in each user database

  • I am trying to complie a script that can automatically loop and create a database level trigger on each user database.

    Here is the script:

    USE master;

    SET NOCOUNT ON;

    DECLARE @db_name nvarchar(50);

    DECLARE @drop_sql nvarchar(1000);

    DECLARE @trg1_sql nvarchar(4000);

    SET @drop_sql = 'IF EXISTS ( SELECT * FROM sys.triggers

    WHERE name = ''trgLogDatabaseSecurityEvents'' )

    DROP TRIGGER trgLogDatabaseSecurityEvents ON DATABASE; ' ;

    DECLARE cur_db_name CURSOR FOR

    SELECT name FROM sys.databases

    WHERE database_id>4

    ORDER BY database_id;

    OPEN cur_db_name;

    FETCH NEXT FROM cur_db_name INTO @db_name

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @drop_sql = ' USE ' + @db_name + '; ' + @drop_sql;

    EXEC sp_executesql @drop_sql;

    SET @trg1_sql = 'CREATE TRIGGER trgLogDatabaseSecurityEvents ON DATABASE

    FOR DDL_DATABASE_SECURITY_EVENTS

    AS

    DECLARE @data XML ;

    DECLARE @hostName nvarchar(100);

    DECLARE @audit_msg varchar(255);

    DECLARE @EventType varchar(100);

    SET @data = EVENTDATA() ;

    SET @EventType = @data.value(''(/EVENT_INSTANCE/EventType)[1]'', ''nvarchar(100)'');

    IF EXISTS (SELECT * FROM Master.dbo.[sysobjects] WHERE name = ''DDLSecurityLog'' AND xtype =''U'')

    select @hostName = host_name FROM sys.dm_exec_sessions s

    where s.session_id = @data.value(''(/EVENT_INSTANCE/SPID)[1]'',''int'');

    INSERT INTO master..DDLSecurityLog

    (

    EventType,

    ObjectName,

    ObjectType,

    DatabaseName,

    tsql , SPID, hostname

    )

    VALUES

    (

    @EventType,

    @data.value(''(/EVENT_INSTANCE/ObjectName)[1]'', ''nvarchar(100)''),

    @data.value(''(/EVENT_INSTANCE/ObjectType)[1]'', ''nvarchar(100)''),

    @data.value(''(/EVENT_INSTANCE/DatabaseName)[1]'', ''nvarchar(100)''),

    @data.value(''(/EVENT_INSTANCE/TSQLCommand)[1]'', ''nvarchar(max)'') ,

    @data.value(''(/EVENT_INSTANCE/SPID)[1]'', ''int''),

    @hostName

    ) ;' ;

    -- SELECT @trg1_sql;

    EXEC sp_executesql @trg1_sql;

    FETCH NEXT FROM cur_db_name INTO @db_name;

    END;

    CLOSE cur_db_name;

    DEALLOCATE cur_db_name;

    The problem here is I can't switch database before running the create trigger statement.

    Because "Create Trigger...needs to be the first statement in a script".

    So the trigger will always be create in master db.

    Any idea how to realize this?

    Thanks.

  • One way out of this is to embed another exec executesql in your @trg1_sql value. For example, if you have:

    set @trg1_sql = 'use ' + @dbname +

    ';exec sp_executesql N''CREATE TRIGGER trgLogDatabaseSecurityEvents

    ON DATABASE etc''';

    and then

    exec sp_executesql @trg1_sql

    then the create trigger part will be a separate batch executing in the context of the correct database. If this is not a convenient solution because you have to double up on your single quotes once more then take a look at the sp_MSForEachDB procedure. It it implemented as a cursor loop through all user databases, so you code will become simpler. Here is the link to discussion: http://www.sqlservercentral.com/Forums/Topic517024-146-1.aspx

    Oleg

Viewing 2 posts - 1 through 1 (of 1 total)

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