dbadude78 (8/31/2012)

---

Yes I have got a DDL trigger, I have disabled it and it works now. Thankyou for your help
The reason i put the triggers was to monitor when a new database is created. Any ideas on how i can set the trigger so that the dbcreator works while its on?

thank you once again

ALTER TRIGGER [TR_DB_NO_DROPPING_OBJECTS]
on DATABASE
FOR 
 DROP_PROCEDURE,DROP_FUNCTION,DROP_VIEW,DROP_TABLE
 AS
 BEGIN
   --only two accounts allowed to drop stuff
   IF suser_name() IN('sa','BobTheSupervisor','mydomain\lowell' )
     BEGIN
       --and only from two specific machines on the network 
       IF host_name() NOT IN('DEV223','PRODUCTION')
         BEGIN
           RAISERROR('Unauthorized use of drop object from inpermissible host.', 16, 1)
           --prevent the drop
           ROLLBACK
         END
     --ELSE --it was the right machine! 
       --BEGIN
         --if it got to here, it was the "right" user from the "right" machine (i hope)
       --END 
     END
   ELSE
     -- not the right login, Susie Ormand style [DENYED]
     BEGIN
        RAISERROR('Unauthorized use of drop object from inpermissible user.', 16, 1)
        --prevent the drop
        ROLLBACK
     END  
END  --DB Trigger