Post reply

Alert for sp_addserver/sp_dropserver ??

  • August 30, 2005 at 6:57 am

    #102753

    Hello.

    I would like to set an MSSQL alert to notify an operator if sp_addserver or sp_dropserver has been used and by who ie: show the NT account. Any suggestions are greatly appreciated.

     

    Many thanks. Jeff

  • August 30, 2005 at 2:47 pm

    #586296

    try a trigger on inserts of sysservers table in the master db. 

  • August 31, 2005 at 4:57 am

    #586405

    I would setup a server side trace and filter TextData column for occurrences of the procedures and periodically check the trace files. Not so elegant, not so proactive but much safer in my opinion.

  • August 31, 2005 at 3:09 pm

    #586665

    Hi, are you able to create a trigger on a system table? I tried and keep getting an error ... "Error 229 CREATE TRIGGER PERMISSION denied" ... and I'm in as sysadmin. Any thoughts?

    ie:

    CREATE TRIGGER [test] ON [dbo].[sysservers]

    FOR INSERT, UPDATE, DELETE

    AS

    EXEC master..xp_sendmail 'username@company.com', 'The trigger works.'

    Many thanks. Jeff

  • September 1, 2005 at 11:54 am

    #586972

    Just curious as to why this is an issue. Only setupadmin and sysadmin have the authority to use this stored procedure. It sounds as if more than just the DBA(s) have elevated rights which may not be needed. Maybe a security audit is in order ???

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • September 2, 2005 at 8:30 am

    #587231

    Hi, we've noticed anomalies on our development servers ie: dropped servers. Could be accidental ie: from code. We cannot control physical access to the database server room 24/7. Developers need access for various other job functions. Any suggestions on implementing a different audit solution (at the database level)?

    Many thanks. Jeff

  • September 2, 2005 at 8:48 am

    #587249

    Hi Jeff,

    Nope, they don't let you create triggers on system tables, probably just as well because come SQL 2005 you wont get access to most of them anyway.

    Getting back to the last post and just as an aside (it is friday afternoon), why not simply pick one of the developers that have been in the DB room at random and make an example of him; it doesn't matter if he actually did it or not because lets face it, he's a developer so he's probably done something else equally bad somewhere else.

    Decapitation may be a little extreme but at my last place of employement we had the 'order of the garden frog', which was awarded to the person making the worst screwup of the month (it was a huge plaster one).  The frog had to sit pride of place on the desk of the 'winner' all month and they had to display a certificate that explained what they'd done.

    Just a thought

  • September 2, 2005 at 9:06 am

    #587258

    Mike ... lol ... that's not bad.

    Many thanks. Jeff

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

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