Potential Issue with Re-Naming Stored Procedures

  • Moe M

    Old Hand

    Points: 364

    Comments posted to this topic are about the item Potential Issue with Re-Naming Stored Procedures

    Moe M
    Database Consultant
    http://www.cubeangle.com

  • Nakul Vachhrajani

    SSChampion

    Points: 10220

    This is bound to happen.

    Per BOL (http://msdn.microsoft.com/en-us/library/ms188351.aspx):

    Changing any part of an object name can break scripts and stored procedures. We recommend you do not use this statement to rename stored procedures, triggers, user-defined functions, or views; instead, drop the object and re-create it with the new name.

    Please always drop & recreate stored procedures.

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • Nakul Vachhrajani

    SSChampion

    Points: 10220

    Nakul Vachhrajani (10/6/2010)


    This is bound to happen.

    Per BOL (http://msdn.microsoft.com/en-us/library/ms188351.aspx):

    Changing any part of an object name can break scripts and stored procedures. We recommend you do not use this statement to rename stored procedures, triggers, user-defined functions, or views; instead, drop the object and re-create it with the new name.

    Please always drop & recreate stored procedures.

    Also, please avoid using syscomments - this will be deprecated in a future version of SQL Server. Use sys.sql_modules instead.

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • Pawel PPA

    SSC Enthusiast

    Points: 120

    Thank you for this article.

    One note regarding to the modifications of stored procedures through drop/create technique.

    When you behave in such a way you will lose security settings for targeted stored procedure.

    For example,

    1. DBA assigned "execute" permission for certain user with name "ExampleUser".

    2. During application update stored procedure has been recreated using drop/create.

    3. User "ExampleUser" is not able to execute this stored procedure as it has been deleted earlier.

    This can be significant issue on Production environment and it will be difficult to explain for end users why permissions have been lost.

    Be aware about this issue.

  • Nakul Vachhrajani

    SSChampion

    Points: 10220

    Petrushenya Pawel (10/7/2010)


    Thank you for this article.

    One note regarding to the modifications of stored procedures through drop/create technique.

    When you behave in such a way you will lose security settings for targeted stored procedure.

    For example,

    1. DBA assigned "execute" permission for certain user with name "ExampleUser".

    2. During application update stored procedure has been recreated using drop/create.

    3. User "ExampleUser" is not able to execute this stored procedure as it has been deleted earlier.

    This can be significant issue on Production environment and it will be difficult to explain for end users why permissions have been lost.

    Be aware about this issue.

    For a production-grade system, you would typically have a list of permissions for each object ready. All that you would then need to do is use the GRANT clause to assign permissions to the stored procedure.

    This would become even easier if you are using User-schema separation wherein users would have permissions on a schema - and then the schema would in-turn have permissions on the object. Because the schema itself is not being dropped/recreated, your user permissions would not need to be reapplied.

    This is exactly what we do in our systems. At the end of the CREATE PROCEDURE, we would always have a GRANT clause to assign whatever permissions that come out-of-the-box with our database.

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • Kelsey Thornton

    SSCrazy

    Points: 2157

    Nakul Vachhrajani (10/7/2010)


    For a production-grade system, you would typically have a list of permissions for each object ready. All that you would then need to do is use the GRANT clause to assign permissions to the stored procedure.

    This would become even easier if you are using User-schema separation wherein users would have permissions on a schema - and then the schema would in-turn have permissions on the object. Because the schema itself is not being dropped/recreated, your user permissions would not need to be reapplied.

    This is exactly what we do in our systems. At the end of the CREATE PROCEDURE, we would always have a GRANT clause to assign whatever permissions that come out-of-the-box with our database.

    Great comment!

    Do we already have an article on "Good Security Practices", or is this maybe a prelude to writing one? 😉

    Kelsey Thornton
    MBCS CITP

  • Ian Ragg

    SSC Veteran

    Points: 274

    I agree with the other comments referring to Books on line. I would add that this "feature" of SQL Server has been around for a very long time and can affect views as well.

    Recently I found that the issue also occurs in Sybase and we know that SQL Server as we know it now is derived from Sybase.

    Ian

  • ben.mcintyre

    SSCommitted

    Points: 1818

    Interesting ... I always noted that if you changed the name of an SP or view in EM for SQL2000 that a similar effect occurred, and you had to double click the SP/view, go behind to the source text and change the name there as well.

    I blamed the GUI at the time, but maybe it's this same bug.

  • Brandie Tarvin

    SSC Guru

    Points: 172757

    Dropping and recreating procs is a regular part of our SDLC. We only ever use rename when we're going to archive off the proc. We want to make sure that nothing using the proc will break, so we add an _old to the end of it.

    But the article raises a good point that I never considered. I should verify with the developers that they aren't using DMO in any of their calls because it might invalidate our "see if it breaks" protocol.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie Tarvin

    SSC Guru

    Points: 172757

    Nakul Vachhrajani (10/6/2010)


    Use sys.sql_modules instead.

    Thanks, Nakul. I didn't even know this table existed.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Jerry Foster

    Newbie

    Points: 9

    Nakul Vachhrajani (10/6/2010)


    Also, please avoid using syscomments - this will be deprecated in a future version of SQL Server. Use sys.sql_modules instead.

    You can also use the really nice OBJECT_DEFINITION() function. Depending on your query it may be easier.

    Jerry

  • Bob Tesch

    Grasshopper

    Points: 15

    The only reason I ever give a plug for a vendor is because I have used the product and it has saved my bacon. Red Gate has a utility that does a "Smart Rename". IF YOU EVER HAVE TO DO RENAMING OF ANYTHING IN THE DATABASE, this utility pays for itself. Good, clean, and complete. It does the job. We actually bought the entire SQL ToolBelt and while it looks expensive, I have paid for it in saved time (and prevented a migration rollback) many times over.

  • Doug Bishop

    SSC Enthusiast

    Points: 119

    I also have used smart rename in SQL Refractor. However, I am a consultant and it is my own property, so not everyone on our team has it.

    It is very common pratice to rename a sotred proc with an _OLD suffix when creating a new version of it. I am in the habit, and have communicated to others, that when renaming procs, they MUST open it in EM, change the name in the CREATE PROC line to match the renamed proc name and save it.

    One of the pitfalls of not doing that is if you ever, as the article pointed out, script the database and need to run the script. SQL Server generates DROP statements for all objects at the beginnng of the script. Subsequently, all objects are then created. You will get an error that the object already exists and then the fun begins.

    I would definately label this as a SQL Server bug and I am very disappointed to see it is still present in SQL Server 2005, especially when using sp_rename. It should take care of renaming the object in syscomments.

  • Jon McGhee

    SSC-Addicted

    Points: 487

    I tested this with SMO scripting (2008), and it worked ok, the renamed procedure scripted with the correct name. After scripting I checked syscomments, and it will still showing the wrong procedure name. Nice to know!

  • Moe M

    Old Hand

    Points: 364

    I didn't get a chance to test it on 2008. Thanks for trying that as you said it is nice to know

    Moe M
    Database Consultant
    http://www.cubeangle.com

Viewing 15 posts - 1 through 15 (of 37 total)

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