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
Follow me on
Twitter: @sqltwins