upstart (7/14/2011)
Well, the reason I am worrying about this one specific table being dropped is because it is used to store information from another DDL trigger we have setup on the database that monitors all database actions.Last week, one of our developers dropped every table/proc/view from the dbo. schema on our development box and either covered their tracks really, really well or our default 'black box' trace for some reason did not capture anything regarding it. We only lost a day of coding since we had a full backup from 1am to restore from. Since then, we implemented 1 DDL trigger to put all database events into a table and want to prevent the developers from dropping it.
So, in essence, if someone decides to drop everything again, at least we will still have this table to tell us what happened. Hmmmm.....unless they delete everything from the table? I may have to think about how to handle that issue as well.
Edit: decided on putting a normal table trigger on this table to prevent deletions. Also, modified the original DDL trigger to not log anything to do with Updating Statistics since it creates about 400 records a night that really serve no purpose and added a weekly job to remove records older then 7 days.
....Auditing...fun for the whole family!
Consider moving your audit tables to a separate database, remove the developers from SYSADMIN, and then add them as members of DBO on the existing database. The problem with SYSADMIN is that it grants them permission to do all sorts of things beyond just creating and dropping objects in a specific database.
Start out by making a copy of the audit tables into the new audit database, create a new test user with same membership as developers currently have, and experiment with the test user until it's all working as needed. Only then alter the existing tables and users.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho