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.

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.

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.

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.

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?

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.

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.