Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Potential Issue with Re-Naming Stored Procedures Expand / Collapse
Author
Message
Posted Wednesday, October 06, 2010 10:58 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 14, 2013 7:41 AM
Points: 30, Visits: 81
Comments posted to this topic are about the item Potential Issue with Re-Naming Stored Procedures

Moe M
Database Consultant
http://www.cubeangle.com
Post #1000118
Posted Wednesday, October 06, 2010 11:55 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 10:17 PM
Points: 1,381, Visits: 1,774
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://beyondrelational.com/modules/2/blogs/77/nakuls-blog.aspx
Be courteous. Drive responsibly.

Follow me on
Twitter: @nakulv_sql
Google Plus: +Nakul
Post #1000130
Posted Wednesday, October 06, 2010 11:56 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 10:17 PM
Points: 1,381, Visits: 1,774
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://beyondrelational.com/modules/2/blogs/77/nakuls-blog.aspx
Be courteous. Drive responsibly.

Follow me on
Twitter: @nakulv_sql
Google Plus: +Nakul
Post #1000131
Posted Thursday, October 07, 2010 12:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:29 PM
Points: 18, Visits: 271
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.


Post #1000136
Posted Thursday, October 07, 2010 12:46 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 10:17 PM
Points: 1,381, Visits: 1,774
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://beyondrelational.com/modules/2/blogs/77/nakuls-blog.aspx
Be courteous. Drive responsibly.

Follow me on
Twitter: @nakulv_sql
Google Plus: +Nakul
Post #1000142
Posted Thursday, October 07, 2010 1:53 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, March 27, 2014 8:16 AM
Points: 534, Visits: 282
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
Post #1000172
Posted Thursday, October 07, 2010 3:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 12, 2013 5:44 AM
Points: 146, Visits: 111

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

Post #1000233
Posted Thursday, October 07, 2010 4:56 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:44 AM
Points: 76, Visits: 379
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.
Post #1000292
Posted Thursday, October 07, 2010 6:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 7,053, Visits: 6,214
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1000341
Posted Thursday, October 07, 2010 6:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 7,053, Visits: 6,214
Nakul Vachhrajani (10/6/2010)
Use sys.sql_modules instead.


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


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1000343
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse