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:-)
well, the only people who can create new databases are those in the sysadmin role, or in dbcreator;
the trigger was created to prevent new databases, so you need to establish the rules, and test accordingly;
for example, I've made DDL triggers where only specific logins, and only from specific hostname(machines) are allowed to perform certain operations...
so only "BobTheSupervisor", myself, or "sa" are allowed, and even then, they must run the command from a specific hostname or IP Address.
, everyone else gets blocked.
something like this example is what i mean:
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
Lowell