April 3, 2012 at 4:12 am
Hi All,
Is it possible to get the details of SP's and Functions altered details. When and which user had altered.
Regards
Guru
April 3, 2012 at 4:16 am
I guess the following can be used for modifying date. However not sure capturing the user who changed it and the entire track of it(only the last modifyed can be collected) unless we have configured for DDL trigger and tracked explicitly.
Select modify_date From sys.procedures
April 3, 2012 at 4:20 am
you will need to build a custom DDL trigger which consumes the XML for the ALTER PROCEDURE and ALTER TRIGGER events and splits out the ALTER command, user and date
this information isnt logged in SQL as standard
April 3, 2012 at 4:21 am
Thank you sqlzealot-81,
But I need to track who changed and its history of changes..
Regards
Guru
April 3, 2012 at 4:26 am
For old entires, I am afraid there is way to get it. Atleast for future, you can create DDL triggers on those procedures you may need to have a tracking method.
April 3, 2012 at 4:32 am
Thank you,
sqlzealot-81
Trying to create for database triggers
Regards
Guru
April 3, 2012 at 5:09 am
The thing to do is to put your database into source control, just like a piece of code. That way you can always track changes. Even if only you use the source control, you can use some of the tools available out there that will let you compare between the existing structure and what is in source control.
Otherwise, you can try using the triggers mechanism, but you're adding load to your system. I'm not a trigger fan if I can solve the problem using some other process.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply