Trace DTS package deletions

Question

Trace DTS package deletions

Andy Poole

SSC Eights!

Points: 817
More actions
June 6, 2003 at 5:43 am

#63303

DTS packages have been deleted from both a sql 7.0 and sql 2k box. I was going to put a sleeping policeman in there (trace), but since you cannot create triggers on a system table (I was going to keep an eye out for deletions on msdb.dbo.sysdtspackages) I was wondering if anybody else has encountered this (from user error) and had to determine "who the guilty party is" ?
Thanks for any assistance.
Andy P

Andy P

Viewing 7 posts - 1 through 6 (of 6 total)

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

Shas3 Hall of Fame Points: 3671 More actions · Answer 1

You can capture the DTS package deletion in the Trace with the following statement. You will see the username, SPID, Time, and Application.

Exec xp_sqltrace Trace, @Fulltext = 1, @EventFilter = 115

This works well in SQL 7.0, for 2K you need to register the sqltrace. dll on the server.

Shas3

Shas3 Hall of Fame Points: 3671 More actions · Answer 2

You will see some thing like

exec msdb..sp_drop_dtspackage

in the Data column of the output

Shas3

Andy Poole SSC Eights! Points: 817 More actions · Answer 3

Thanks for that.....I'll try and get this scheduled in asap.

Andy P

mccork SSC-Insane Points: 22288 More actions · Answer 4

Andy,

sysdtspackages isn't a system table in the same sense as system tables within master and model. Your can define triggers on them. Try the following for logging the activities to the SQL error log.

 

CREATE TRIGGER tr_sysdtspackages

ON msdb.dbo.sysdtspackages

FOR DELETE, INSERT, UPDATE 

AS 

BEGIN

    DECLARE @msg VARCHAR(500)

    DECLARE mods CURSOR FOR

        SELECT 

            CASE

                WHEN inserted.name IS NULL THEN 'Delete'

                WHEN deleted.name IS NULL THEN 'Insert'

                ELSE 'Update'

            END

            + ' action on package '

            + COALESCE(inserted.name, deleted.name)

            + ' by ' + SUSER_SNAME()

            + ' (id=' + CONVERT(VARCHAR(50), COALESCE(inserted.id, deleted.id))

            + ',versionid=' + CONVERT(VARCHAR(50), COALESCE(inserted.versionid, deleted.versionid))

            + ')'

        FROM

            inserted FULL OUTER JOIN deleted

            ON inserted.id=deleted.id AND inserted.versionid=deleted.versionid

    OPEN mods

    WHILE 1=1 BEGIN

        FETCH NEXT FROM mods INTO @msg

        IF @@FETCH_STATUS <> 0 BREAK

    RAISERROR ('%s', 0, 1, @msg) WITH LOG

    END

    CLOSE mods

    DEALLOCATE mods

END

Cheers,
- Mark

Andy Poole SSC Eights! Points: 817 More actions · Answer 5

Thanks for that.....

That's the best way I've been called a muppet yet !

Seriously though......greatly appreciated.

Regards

Andy P

Andy Poole SSC Eights! Points: 817 More actions · Answer 6

Andy Poole

SSC Eights!

Points: 817

July 2, 2003 at 8:31 am

#463931

That worked a treat.....thanks !

Andy P