SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 How to track procedure changes?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SreenivasBora
Posting Yak Master

USA
164 Posts

Posted - 10/12/2005 :  15:44:45  Show Profile  Reply with Quote
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
35940 Posts

Posted - 10/12/2005 :  15:59:41  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

SreenivasBora
Posting Yak Master

USA
164 Posts

Posted - 10/12/2005 :  16:20:26  Show Profile  Reply with Quote
So denying alter permissions is the best way?

With Regards
BSR
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 10/12/2005 :  16:22:38  Show Profile  Visit tkizer's Homepage  Reply with Quote
How do you deny ALTER permissions?

Tara
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 10/12/2005 :  16:39:06  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 10/12/2005 :  16:44:32  Show Profile  Visit tkizer's Homepage  Reply with Quote
Isn't this happening in a dev environment in the first place?

Tara
Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 10/12/2005 :  22:16:53  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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...
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/13/2005 :  10:11:50  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 10/13/2005 :  12:30:24  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 10/13/2005 :  23:02:45  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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...
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 10/14/2005 :  12:33:32  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

scullee
Posting Yak Master

Australia
103 Posts

Posted - 10/16/2005 :  08:05:08  Show Profile  Reply with Quote
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.
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 10/16/2005 :  09:38:07  Show Profile  Visit Merkin's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000