Post reply

What are the ways to prevent alter procedure?

  • July 18, 2010 at 10:22 pm

    #235338

    hi,

    How can prevent the alter procedure when one developer is altering the procedure?

    Thanks,

    🙂

  • July 18, 2010 at 10:58 pm

    #1194703

    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?

  • July 18, 2010 at 11:13 pm

    #1194706

    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?

    🙂

  • July 18, 2010 at 11:40 pm

    #1194709

    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.

  • July 18, 2010 at 11:52 pm

    #1194712

    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

  • July 19, 2010 at 10:57 am

    #1195018

    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.

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

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