| Author |
Topic |
|
igorblackbelt
Constraint Violating Yak Guru
407 Posts |
 Posted - 2005-03-09 : 16:24:20
|
| Hello Guys,Is there a table in Master that tells me changes ocurred on any DBs in my Server and gives me the user ID in charge for the change ?You guys see where I want to go, right ?Thanks!Igor. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-09 : 16:25:38
|
| No there is not. You would have to log this information yourself via triggers or some other mechanism or view this information in the tlog using a third party tool, such as Lumigent's Log Explorer.Tara |
 |
|
|
igorblackbelt
Constraint Violating Yak Guru
407 Posts |
Posted - 2005-03-09 : 16:33:42
|
| How about create a Trigeer on the sys databases that sends out an email with the user ID if a table is created ? Sounds possible ?Igor. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-03-09 : 16:33:55
|
| Are you trying to track Data Changes or Object Changes or both?Brett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-09 : 16:34:57
|
quote:
Originally posted by igorblackbelt
How about create a Trigeer on the sys databases that sends out an email with the user ID if a table is created ? Sounds possible ?Igor.
You can't create triggers on system tables. It is not allowed.Why can't you handle this through training or remove people's permissions who shouldn't be doing this in the first place?Tara |
|
|
|
igorblackbelt
Constraint Violating Yak Guru
407 Posts |
Posted - 2005-03-09 : 16:44:47
|
| 1) Yes, both, but at this point, we need more objects changes auditing.2) I understand Tara, but as I said before, it get's political...Igor. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-09 : 16:46:46
|
| For data changes, you can create triggers on your tables and log the information to audit tables. Brett has information about this in his weblog.For object changes, well you'll probably have to be running SQL Profiler at all times in order to catch this unless you purchase a third party tool.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-03-09 : 16:55:52
|
| Or waiting for SQL 2005 which will Object level triggershttp://weblogs.sqlteam.com/brettk/archive/2004/10/20/2242.aspxBrett8-)EDIT: To be honest though, you should have control over the database...give developers their own db, and if they have changes that have to go, they should give the ddl to you for review, and then you run the script |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-03-09 : 18:18:10
|
Triggers are not the ideal tool for solving a political problem.quote:
Originally posted by igorblackbelt
1)...2) I understand Tara, but as I said before, it get's political...
CODO ERGO SUM |
|
|
|
|
Master DB