| Author |
 Topic  |
|
|
SreenivasBora
Posting Yak Master
USA
164 Posts |
 Posted - 10/12/2005 : 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 Regards
BSR |
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
Posted - 10/12/2005 : 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
USA
164 Posts |
Posted - 10/12/2005 : 16:20:26
|
So denying alter permissions is the best way?
With Regards
BSR |
|
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
Posted - 10/12/2005 : 16:22:38
|
How do you deny ALTER permissions?
Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 10/12/2005 : 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 instance
Brett
8-)
Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
Posted - 10/12/2005 : 16:44:32
|
Isn't this happening in a dev environment in the first place?
Tara |
|
|
|
jen
Flowing Fount of Yak Knowledge
Sweden
4110 Posts |
Posted - 10/12/2005 : 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 made
HTH 
quote:
Originally posted by SreenivasBora
So denying alter permissions is the best way?
With Regards
BSR
--------------------
keeping it simple... |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 10/13/2005 : 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'
GO
EXEC dbo.kk_SP_LogScriptRun 'MySProc', '051013'
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[MySProc]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.MySProc
GO
CREATE PROCEDURE dbo.MySProc
... contents here ...
--================== MySProc ==================--
GO
IF exists (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[MySProc]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
GRANT EXECUTE ON dbo.MySProc TO MyGroup
GO
PRINT '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 MyGroup
is 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 between
PRINT 'Create procedure MySProc'
and
PRINT '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
USA
35017 Posts |
Posted - 10/13/2005 : 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 made
HTH
quote:
Originally posted by SreenivasBora
So denying alter permissions is the best way?
With Regards
BSR
--------------------
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
Flowing Fount of Yak Knowledge
Sweden
4110 Posts |
Posted - 10/13/2005 : 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 made
HTH
quote:
Originally posted by SreenivasBora
So denying alter permissions is the best way?
With Regards
BSR
--------------------
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
USA
35017 Posts |
Posted - 10/14/2005 : 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
Australia
103 Posts |
Posted - 10/16/2005 : 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!
Australia
4970 Posts |
Posted - 10/16/2005 : 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 |
|
|
| |
Topic |
|
How to track procedure changes?
