What are the ways to prevent alter procedure?

Question

What are the ways to prevent alter procedure?

SQL*

SSCertifiable

Points: 6638
More actions
July 18, 2010 at 10:22 pm

#235338

hi,
How can prevent the alter procedure when one developer is altering the procedure?
Thanks,
🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 1

While you can use DDL triggers to capture the ALTER event, I'm curious as to the circumstances that would warrant this. Are you trying to force a particular developer to only use DROP PROCEDURE and then CREATE PROCEDURE statements, or don't you want him/her to to have permission to change a particular procedure?

SQL* SSCertifiable Points: 6638 More actions · Answer 2

Thanks for your prompt,

If both developers can have the same permissions ( i.e., single sql user), then they are doing modifications at the same time, then how can i prevent one developer when one is altering the procedure?

🙂

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 3

Ah, that makes sense. Unfortunately, you can't (easily), from a technical standpoint - it's very much a management issue. Even if the two developers had different logins, it would be very easy for Developer A to overwrite Developer B's changes, and neither of them would know about it. With only a single login (why?), it's even more difficult.

One way of avoiding this is to use source control. If the master version of each stored procedure is a .sql file stored in your source control system, then the person with the file checked out for editing will have control. This might not work as well if you have source control systems that doesn't allow you to lock files, however.

Another method would be to let each developer develop with their own copy of the database, but you then need frequent merge operations to make sure that they're not stepping on each other's toes.

Another option would be to completely lock down the database, and only grant each developer access to the specific procedure he'd like access to. This does require multiple logins, however, and a lot of work for the sysadmin.

Ultimately, it comes down to communication. While you could add a DDL trigger to log every modification, and ring alarm bells if a modification was already made, for example, in the past 24 hours, it would probably be easier to modify your methodology to avoid these types of clashes.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Source control?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Geoff A SSChampion Points: 11511 More actions · Answer 5

GilaMonster (7/18/2010)
Source control?

sounds like a "roll code" tool. probably a custom tool / process not described very well. We have some apps with such tools that involve NTFS permissions, check in / out procedures, blah, blah, blah.... very cumbersome.