November 4, 2009 at 3:00 am
I have written a DDL trigger to log the changes in the database in a Logtable on a separate databae. Things are running very smoothly.
Now the issue is what if someone deletes my DDL trigger. Delete DDL Trigger operation is not recorded in my Logtable.
I need to have an alert or atleast recorded somewhere if someone drops it. I have checked "ALTER ANY DATABASE DDL TRIGGER" but it locks complete database and user are not allowed to Alter or Create any thing.
I know I am missing something.
Thanks in advance.
November 4, 2009 at 4:48 pm
Hi,
I wrote a simple test and it seems to be working for me, please run it in your environment:
create login test with password='test123'
go
use testdb
go
create user utest from login test
go
--add user to db_owner to see what happens
exec sp_addrolemember 'db_owner', 'utest'
go
create trigger ddltrig
on database
for ddl_database_level_events
as
raiserror('trigger called!', 10, 1)
go
--test if trigger is working
create table t(a int)
drop table t
go
--deny right to disable or drop this trigger to the new user
deny ALTER ANY DATABASE DDL TRIGGER to utest
go
--impersonate utest user - or open new window and log on as test login
execute as user='utest'
go
--check security context
print user_name()
go
create table tt(c int) --this works
go
drop table tt --this works
go
--try to disable trigger
disable trigger ddltrig on database --this doesn't
go
--try to drop trigger
drop trigger ddltrig on database --neither this
go
--revert to original scope
revert
go
--check security context
print user_name()
--CLEANUP
drop user utest
go
drop login test
go
drop trigger ddltrig
on database
Regards
Piotr
...and your only reply is slàinte mhath
November 4, 2009 at 7:28 pm
I'm fairly sure that this won't prevent a sysadmin user from removing your trigger, but should help at the DB level.. I'd probably do the DDL trigger at the server level anyway.. But that is me..
CEWII
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply