Auditing Stored Procedure definition
-
Dear Friends,
For One of the project we have team of 20 people using SQL 2000 database.
As part of developement we mostly need to modify definition of storedprocedure/function. Sometime, we lost track when perticuler sp/fun was modified and it's definition.
As we can use trigger on table. Is there any way to Audit definition of SP/function before modification ?
Any other suggestion or guidance is welcome
.Thanks in Advance.
-
It is generally not recommended to place triggers on the system tables in SQL Server 2000. Therefore, managing stored procedure and DDL changes is a manual process. I would recommend using some sort of versioning tool such as Microsoft Visual Source Safe. When 20 developers are working in the same environment, you need a way to make sure that only one developer is working on an object at a time and that you have a history of object versions. You also need to define and document a process that will ensure that changes are not lost or forgotten. Everyone involved needs to become accountable for their actions in reference to that process.
-
Adding on from John's response, we use Visual Source Safe (VSS) and if you add the following Keyword Expansion Header to the top of all your scripts VSS will fill in all the details everytime the file is checked in and you will get a nice history of changes in the file when you open it. Note keyword expansion needs to be enabled by your VSS administrator as it's disabled by default for all file types. Look up 'Expand Keywords' in VSS help.
/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Description:
Application:
$Workfile: $
$Header: $
Revision History:
$History: $
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
A sample of a files history is below (replaces the $History: $ variable in the file with the historical comments), this is populated from the Check-in comments you add to the file when checking it in to VSS.
*
* ***************** Version 5 *****************
* User: Lholman Date: 26/06/06 Time: 15:13
* Updated in $/DBTeam Operations/CIM/CIM Database/Create Scripts/Stored Procedures
* Updated permissions to GRANT EXECUTE permissions to [NESTA\SQL Server
* Staging - CIM - Users DL] for UAT in STAGING\CIM database
*
* ***************** Version 4 *****************
* User: Lholman Date: 18/04/06 Time: 13:39
* Updated in $/DBTeam Operations/CIM/CIM Database/Create Scripts/Stored Procedures
* Amended to remove co_id input parameter
*
* ***************** Version 3 *****************
* User: BOtter Date: 13/04/06 Time: 16:36
* Updated in $/DBTeam Operations/CIM/CIM Database/Create Scripts/Stored Procedures
* Amended variable typo
*
* ***************** Version 2 *****************
* User: Lholman Date: 13/04/06 Time: 16:24
* Updated in $/DBTeam Operations/CIM/CIM Database/Create Scripts/Stored Procedures
* Amended to rename and include standard header, standard error handling
* and check for invalid parameter values
*
* ***************** Version 1 *****************
* User: BOtter Date: 4/04/06 Time: 12:35
* Created in $/DBTeam Operations/CIM/CIM Database/Create Scripts/Stored Procedures
* Needed to ensure procedure only allowed a contact to delete an email
* address they own
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply