September 18, 2009 at 12:56 pm
Today, I got an error saying a primary key was violated with a duplicate value. The primary key is on an identity column, and I found it was seeded to 1. This is not the first time this happened. Besides me, 3 developers and my boss (mostly non-technical) have administrative access, and there is a website and other code running against the database too.
Can I find out who was responsible for resetting this value?
If not, what can put in place to catch them next time?
I am using SQL 2005 Standard.
September 18, 2009 at 1:10 pm
You may want to use DDL trigger on that table.
It could be INSTEAD OF or AFTER trigger.
"INSTEAD OF" trigger will prevent any ALTER TABLE operations, but "AFTER" trigger can catch a user, who modified a table. It is up to you which one to use.
September 18, 2009 at 1:17 pm
Unfortunately, it isn't always the same table, and I don't want to stop the other 3 from making legit changes.
September 18, 2009 at 1:27 pm
In this case you may want to create AFTER trigger on the whole database and catch all structural modifications into audit table.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply