| Author |
Topic |
|
koln5
Starting Member
29 Posts |
 Posted - 2007-10-12 : 15:32:19
|
| Does SQL Server logs track such a thing ?Where do I look to see when the last time a stored procedure was modified or deleted....or the same goes for a table created ?Thank you |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-10-12 : 15:54:10
|
| there's a creation date for any object. I dont think theres any modified date.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-10-12 : 15:54:30
|
| you can prbly write a DML trigger to track such changes.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
koln5
Starting Member
29 Posts |
Posted - 2007-10-12 : 15:56:52
|
| yea, Im aware of the create date, but no log automatically tracks when a stored procedure/table is modified or deleted ? |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-10-12 : 16:48:27
|
quote:
Originally posted by X002548
quote:
Originally posted by dinakar
there's a creation date for any object. I dont think theres any modified date.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/
You kidding?That was a big thing about 2k5I would never know, because I always Drop and createIn any case there are DDL Triggers in 2k5Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam
Thats what I thought too.. so I looked up before I posted. I did an sp_help on a table and a proc and didnt see any modified date. Now after you mentioned it again, I took some time and looked in sys.objects and yes there is a modify_date in it. SELECT Modify_Date,* FROM Sys.objects Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-10-12 : 16:49:14
|
| But, if an object is deleted I guess its gone from sys.objects too. So its back to DDL triggers.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-12 : 17:52:34
|
| "How do I track when a Stored Proc. was deleted ?Does SQL Server logs track such a thing ?"Only if you have set up a process specifically to track such events."Where do I look to see when the last time a stored procedure was modified or deleted....or the same goes for a table created ?"There is a Modified data for such objects. There is no deleted date (because the object is gone! unless you have taken steps to audit such events, as mentioned above).If this is a mission critical application with a security breech you use backup files to work out when the breech occurred. Assuming you have TLog backups, rather than just Full database backups [which is normal for a mission-critical database] then you would be able to narrow this down to the exact time it happened.But that will take a lot of effort, and it isn;t going to happen unless there is a BIG reason to conduct the enquiry!If this is a general question then:Store all your source code in a source code repository / version control. (We use Sub Version & Tortoise, but there are numerous flavours out there). Then it won't matter if an Sproc gets deleted, 'coz you will have a complete history of all the edits made to that Sproc and will be able to reproduce that specific version from your Version Coronal repository.Kristen |
|
|
|
koln5
Starting Member
29 Posts |
Posted - 2007-10-12 : 19:15:50
|
| thank you to everybody ! |
|
|
|
|
How do I track when a Stored Proc. was deleted ?