Reprinted with permission from owner. Original at http://nlsimmons.com/SQLScraps/
Recently I had to search for, and update, hard-coding server references for every stored procedure in a database. This amounted to altering over 60 stored procedures. Instead of going through each stored procedure, one-by-one, I used the following code to accomplish the task:
Reprinted with permission from owner. Original at http://nlsimmons.com/SQLScraps/
When scripting stored procedure code for deployment to several servers, there are a few things to keep in mind. One of those things is the permissions associated with the procedure you’re deploying. When deploying a brand new stored procedure, the process of assigning permissions is fairly straightforward. If you need to deploy an update to an existing stored procedure, things get a bit more complicated. If you simply issue a DROP PROCEDURE call followed by the CREATE PROCEDURE code, you will lose the permissions that are associated with the stored procedure. Issuing an ALTER PROCEDURE command will preserve the permissions but will cause you trouble if there are places where the procedure doesn’t yet exist. To solve this issue, you should script out the permissions on a procedure to a temp table before dropping the procedure. Keep reading…