| Author |
Topic |
|
SreenivasBora
Posting Yak Master
164 Posts |
 Posted - 2005-10-12 : 15:44:45
|
| Hi ALL,We are working on Multiple databases from different Locations. Developers are modifying the Stored Procedures very frequently.We are unable to figure out when was the procedure was changed? When I opened from Enterprise Manager, it is showing procedure created date but not modified date.Can any one guide me how you guys have answer me for the below ....1. How do we track the procedure changes? who did the change and when is Very IMP?2.Is there any way to write a Trigger on sysobjects/sysmessages to identify the before and after changes?3. Does VSS (sourcesafe) will do any help for the above?With RegardsBSR |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-12 : 15:59:41
|
| When a developer uses ALTER PROC, there is no way to track when a change was made unless you were running SQL Profiler during that time. However, SQL Server 2005 comes with this ability, which is great news!You can't create triggers on system objects.VSS would certainly help but it wouldn't prevent developers from making the change in the database and not checking it into source control.Tara |
 |
|
|
SreenivasBora
Posting Yak Master
164 Posts |
Posted - 2005-10-12 : 16:20:26
|
| So denying alter permissions is the best way?With RegardsBSR |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-12 : 16:22:38
|
| How do you deny ALTER permissions?Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-10-12 : 16:39:06
|
| You should have a scratch pad for developers....a separate development instance where only you have control...a qa instance which could be the same as the second dev, and your prod instanceBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-12 : 16:44:32
|
| Isn't this happening in a dev environment in the first place?Tara |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-10-12 : 22:16:53
|
deny them dbo rights and if this is not yet in the dev environment,you can create a program that will allow them to modify objects and still keep track of them.The idea is to prevent them from changing objects without an audit trail, so they won't have any choice but to use the program you madeHTH  quote:
Originally posted by SreenivasBora
So denying alter permissions is the best way?With RegardsBSR
--------------------keeping it simple... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-13 : 10:11:50
|
Here's what we do in case it is of interest:Each of our SProcs is in a separate file - no-one is allowed to change an Sproc in, say, EM using RightClick : Properties.Each file has:--PRINT 'Create procedure MySProc'GOEXEC dbo.kk_SP_LogScriptRun 'MySProc', '051013'GOIF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[MySProc]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE dbo.MySProcGOCREATE PROCEDURE dbo.MySProc... contents here ...--================== MySProc ==================--GOIF exists (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[MySProc]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) GRANT EXECUTE ON dbo.MySProc TO MyGroupGOPRINT 'Create procedure MySProc DONE'GO We concatenate multiple files together to make release scripts (e.g. to patch to current version). Thus each file starts with "--" in case the previous file did not finish with a blank line!EXEC dbo.kk_SP_LogScriptRun 'MySProc', '051013'logs that the Sproc was run, and its version number (for which we use today's date). We change this whenever we make a significant change [obviously this is open to "forgetting" ...]We can then compare the Script Run Log on QA with PRODUCTION to work out what is different / what needs to be applied.The Script Run Log also gives us a history of what was run.Running:EXEC dbo.kk_SP_LogScriptRun 'MySProc'(i.e. highlight ALL BUT the version bit on the end, and execute, in QA) shows the history and current CRDATE on the server. So if you have the script open in QA you can just highlight that bit and see what's what.The--================== MySProc ==================--bit is so that when multiple SProc source code is concatenated into one file its easy to see where a specific SProc code ends.GRANT EXECUTE ON dbo.MySProc TO MyGroupis used so that all the permissions are stored together with the source, and will just be run on QA / PRODUCTION the same as DEV was.The PRINT statements are to show the Start/End execution - again when source code for multiple SProcs is concatenated into a single file, any errors that are displayed will clearly be betweenPRINT 'Create procedure MySProc'andPRINT 'Create procedure MySProc DONE'so it is easy to see which SProc creation was generating the error - you can double click a Syntax Error in QA, but you can't double click a "sysdepends" warning ...Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-13 : 12:30:24
|
quote:
Originally posted by jen
deny them dbo rights and if this is not yet in the dev environment,you can create a program that will allow them to modify objects and still keep track of them.The idea is to prevent them from changing objects without an audit trail, so they won't have any choice but to use the program you madeHTH quote:
Originally posted by SreenivasBora
So denying alter permissions is the best way?With RegardsBSR
--------------------keeping it simple...
Developers should have dbo permissions in a dev environment. They need to be able to create dbo objects. Why is an audit trail required for a dev environment? That's what source control is for.Tara |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-10-13 : 23:02:45
|
quote:
Originally posted by tduggan
quote:
Originally posted by jen
deny them dbo rights and if this is not yet in the dev environment,you can create a program that will allow them to modify objects and still keep track of them.The idea is to prevent them from changing objects without an audit trail, so they won't have any choice but to use the program you madeHTH quote:
Originally posted by SreenivasBora
So denying alter permissions is the best way?With RegardsBSR
--------------------keeping it simple...
Developers should have dbo permissions in a dev environment. They need to be able to create dbo objects. Why is an audit trail required for a dev environment? That's what source control is for.Tara
--------------------keeping it simple... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-14 : 12:33:32
|
| I guess I was confused by the word "and" in there. "deny them dbo rights" seemed to stand alone in the sentence apart from development. Oh well, sorry for confusion.Tara |
|
|
|
scullee
Posting Yak Master
103 Posts |
Posted - 2005-10-16 : 08:05:08
|
| The way we control it is to save each of the stored procs in a file in CVS, at the top of the file there is $log$ so CVS writes details each time the file is committed. All push ups are done through an ANT build script which drops all the procs in the database then recreates them from the ones in CVS. This way if the code isnt checked in, it doesnt go up or is at least deleted next time we do a push up.This has caught a few of the developers out when they try to do dodgy things but after a while they get the picture and follow the process. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2005-10-16 : 09:38:07
|
quote:
Originally posted by scullee
The way we control it is to save each of the stored procs in a file in CVS, at the top of the file there is $log$ so CVS writes details each time the file is committed. All push ups are done through an ANT build script which drops all the procs in the database then recreates them from the ones in CVS. This way if the code isnt checked in, it doesnt go up or is at least deleted next time we do a push up.This has caught a few of the developers out when they try to do dodgy things but after a while they get the picture and follow the process.
Quoting all of this to highlight my total agreement!!!!!I do the same thing, and tell people "If your code isn't in CVS, it doesn't exist and it WILL disappear". Damian"A foolish consistency is the hobgoblin of little minds." - Emerson |
|
|
|
|
How to track procedure changes?