Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2007
    Posts
    41

    Unanswered: SQL Server Job Agent Logging !!

    Hi pals,

    I am using sql server 2005.

    Is there any means to check/see the log if i add a new job category has been added or deleted .

    I just want to know this for Auditing purpose.
    i just want to keep a track what new job categories have been deleted or new Job categories have been added on so and so dates.


    Hint:
    -----

    Management Studio-->Jobs-->Right Click-->Manage Job Categories-->Add/Delete.

    Any Thoughts????

  2. #2
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194
    Hi Frank.

    You can create an insert and update trigger on SYSCATEGORIES table in MSDB database for tracking.


    example:
    -- assuming an admin db exists, otherwise replace the admin with the db in which tracking history to maintain.
    use msdb
    go
    create table admin.dbo.track_Job_category (category [varchar] (8000), inserted bit default 0, deleted bit default 0, event_user [varchar] (50), event_date datetime)
    go
    create trigger tr_track_insert_category on msdb.dbo.syscategories
    after insert
    as
    insert admin.dbo.track_job_category (category, inserted, event_user, event_date)
    select [name], 1, current_user, current_timestamp from inserted
    go

    create trigger tr_track_delete_category on msdb.dbo.syscategories
    after delete
    as
    insert admin.dbo.track_job_category (category, deleted, event_user, event_date)
    select [name], 1, current_user, current_timestamp from deleted
    Last edited by thebeginner; 04-21-08 at 10:23.
    Cheers!





    !Direction is More Important than Speed!

    http://talatnauman.blogspot.com/

  3. #3
    Join Date
    Apr 2008
    Posts
    4
    Check msdb.dbo.syscategories

    SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'category-name'

  4. #4
    Join Date
    Nov 2007
    Posts
    41
    Thank You Very Much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •