Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sgtwilko
Starting Member
23 Posts |
 Posted - 2002-03-26 : 12:27:19
|
| I would like to be able to track changes to the structure of a database using triggers.If this is not possible, tracking who made chages would be almost as good.I think that SQL server writes to system tables about the changes being made, but I can't find which tables it is. If I could find the tables, I could create triggers that could track the users doing the changes.Thanks for any help.Ian.-- Eagles may soar,but Weasels aren't sucked into jet engines. |
|
|
dsdeming
479 Posts |
Posted - 2002-03-26 : 12:33:35
|
| Changes would be made primarily in the sysobjects and syscolumns, but I don't think I would put triggers on system tables. I'd more likely maintian a snapshot in some separate tables and compare that to a nightly snapshot to find the changes. |
 |
|
|
Jay99
468 Posts |
Posted - 2002-03-26 : 12:45:47
|
| What you need is a server trace capturing and recording ddl. Not to let the cat out of the bag, but I am working on writing an article on just this very subject.Jay<O> |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-26 : 12:46:29
|
| A. DO NOT ADD TRIGGERS TO sysobjects OR syscolumns! I don't think SQL Server will let you anyway, but DO NOT play with system tables:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12672B. Create a trace in Profiler that looks for "Object:Created". I don't know if this fires for an ALTER statement though. You could also monitor T-SQL actions, but this will capture everything. Profiler will also add overhead to your SQL Server and could slow it down significantly.C. Why not revoke CREATE/ALTER table permissions to everyone except the database owner? This is really how it SHOULD be anyway; the average user shouldn't be modifying a production table. If it's a development machine, you could relax this a bit, but you really can't track these kinds of changes if people can modify anything at their whim. |
|
|
|
sgtwilko
Starting Member
23 Posts |
Posted - 2002-03-26 : 13:34:02
|
Hiya,I would have thought that the check error was simply because those names are used elsewhere and they did not match...quote:
B. Create a trace in Profiler that looks for "Object:Created". I don't know if this fires for an ALTER statement though. You could also monitor T-SQL actions, but this will capture everything. Profiler will also add overhead to your SQL Server and could slow it down significantly.
Too much overhead.quote:
C. Why not revoke CREATE/ALTER table permissions to everyone except the database owner? This is really how it SHOULD be anyway; the average user shouldn't be modifying a production table. If it's a development machine, you could relax this a bit, but you really can't track these kinds of changes if people can modify anything at their whim.
Can't do that.We are trying to catch someone making changes to our development database.We don't want to just ban them, we want to know who they are.Also if it is who we think it is, they could reset their permissions.So any other ideas then?Thanks againIan.-- Eagles may soar,but Weasels aren't sucked into jet engines. |
|
|
|
Jay99
468 Posts |
Posted - 2002-03-26 : 13:49:15
|
quote:
...Too much overhead...
I'm curious, how much over your 'overhead limit' would this solution put you?I run a 'Big Brother is Watching' trace on all my dev servers. I find the ablility to know who-changed-what-last far out-weights any performance hit.Jay<O> |
|
|
|
sgtwilko
Starting Member
23 Posts |
Posted - 2002-03-26 : 14:39:39
|
quote:
I'm curious, how much over your 'overhead limit' would this solution put you?
Well from my (very quick) test it slowed things down to a standstill.If you have a template that you can send to me, I would apprechiate it.ThanksIan.-- Eagles may soar,but Weasels aren't sucked into jet engines. |
|
|
|
Jay99
468 Posts |
Posted - 2002-03-26 : 15:10:09
|
| How bout you provide the xp_trace_* calls you used too create the trace that brought everything to a standstill . . . or if you used the Profiler GUI, tell us what you used for Trace Properties. Maybe you are capturing more than you need.I have authored traces that capture every command run into a database along with who, from where, execution time etc. I had the trace dump into a table. That was the only time I have ever seen a trace impact performance and the database was still usable.Jay<O> |
|
|
|
|
|
|
|
|
Tracking Structure Changes with Triggers